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]

Bryan Harrington wrote:

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
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Chris Hohmann]

"Bryan Harrington" <ne**@psacake.c om> wrote in message
news:OZ******** ******@TK2MSFTN GP12.phx.gbl...

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.datab ase 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]

On Tue, 16 Dec 2003 12:16:50 -0500, "Bryan Harrington"
<ne**@psacake.c om> wrote:

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...

--
Aaron Bertrand
SQL Server MVP