Velocity Reviews

Velocity Reviews (http://www.velocityreviews.com/forums/index.php)
-   ASP General (http://www.velocityreviews.com/forums/f65-asp-general.html)
-   -   Select top 40 * from and Order By (http://www.velocityreviews.com/forums/t792194-select-top-40-from-and-order-by.html)

Bryan Harrington 12-16-2003 05:16 PM

Select top 40 * from and Order By
 
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 12-16-2003 06:40 PM

Re: Select top 40 * from and Order By
 
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 12-16-2003 07:03 PM

Re: Select top 40 * from and Order By
 
"Bryan Harrington" <news@psacake.com> wrote in message
news:OZjkDh$wDHA.2456@TK2MSFTNGP12.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.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 12-16-2003 10:05 PM

Re: Select top 40 * from and Order By
 
On Tue, 16 Dec 2003 12:16:50 -0500, "Bryan Harrington"
<news@psacake.com> 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 12-16-2003 10:23 PM

Re: Select top 40 * from and Order By
 
> 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




All times are GMT. The time now is 06:33 PM.

Powered by vBulletin®. Copyright ©2000 - 2014, vBulletin Solutions, Inc.
SEO by vBSEO ©2010, Crawlability, Inc.