Select top 40 * from and Order By

[Bryan Harrington]

Can you use Select top 40 * from ... and ORDER BY... in the same query?

I have the following 4 quearies that are.. for the most part, the same
except the order by clause, all return a differnt amount of records.
Thoughts?

select top 40 * from q_work_list where assigned_to = 10 order by batch_date,
remit_amt desc
40 records returned.


Sort by Reject Amt
select top 40 * from q_work_list where assigned_to = 10 Order by remit_amt
asc
41 records returned.

Sort By Batch Date
select top 40 * from q_work_list where assigned_to = 10 Order by batch_date
desc
110 records returned.


Sort by Batch Again
select top 40 * from q_work_list where assigned_to = 10 Order by batch_date
asc
143 records returned.

And it doesn't make a difference if I use * or the appropriate field names..
same results.

TIA

 

[Bob Barrows]

Can you use Select top 40 * from ... and ORDER BY... in the same
query?

It depends on the database, and sometimes the version of the database <hint>

Bob Barrows

 

[Chris Hohmann]

Can you use Select top 40 * from ... and ORDER BY... in the same query?

I have the following 4 quearies that are.. for the most part, the same
except the order by clause, all return a differnt amount of records.
Thoughts?

select top 40 * from q_work_list where assigned_to = 10 order by batch_date,
remit_amt desc
40 records returned.


Sort by Reject Amt
select top 40 * from q_work_list where assigned_to = 10 Order by remit_amt
asc
41 records returned.

Sort By Batch Date
select top 40 * from q_work_list where assigned_to = 10 Order by batch_date
desc
110 records returned.


Sort by Batch Again
select top 40 * from q_work_list where assigned_to = 10 Order by batch_date
asc
143 records returned.

And it doesn't make a difference if I use * or the appropriate field names..
same results.

TIA

In future posts, please provide database, version and appropriate DDL
when asking a database related question. Also consider posting the
message to the m.p.i.asp.database group instead.

The behavior of the queries seems to indicate that you are using MS
Access. In MS Access, the TOP predicate of the SELECT clause carries an
implicit WITH TIES. So in your second example, both the 40th and the
41st records have the same Reject Amt. Similarly in your 3rd example
records 40-110 all have the same Batch Date.

HTH
-Chris Hohmann

 

[Jeff Cochran]

Can you use Select top 40 * from ... and ORDER BY... in the same query?

Yes. Or no. (Depends on database and version...)

I have the following 4 quearies that are.. for the most part, the same
except the order by clause, all return a differnt amount of records.
Thoughts?

TOP 40 might return more than 40 on an ORDER BY if you have duplicates
in whatever field you order by. In other words, if you ORDER BY Price
and have 81 items at $1.00, *which* are the top 40?

Jeff

 

[Aaron Bertrand - MVP]

TOP 40 might return more than 40 on an ORDER BY if you have duplicates

in whatever field you order by. In other words, if you ORDER BY Price
and have 81 items at $1.00, *which* are the top 40?

In SQL Server at least, you can alter this behavior by the optional WITH
TIES clause. I think Access just uses WITH TIES by default and you can't
override it, but I'm not certain; likewise, I'm not sure if similar optional
clauses are available in other RDBMSes.

And FWIW, TOP without ORDER BY makes absolutely no sense in a relational
database. You may as well say TOP 40 ARBITRARY...