Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Select top 40 * from and Order By

Reply
Thread Tools

Select top 40 * from and Order By

 
 
Bryan Harrington
Guest
Posts: n/a
 
      12-16-2003
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


 
Reply With Quote
 
 
 
 
Bob Barrows
Guest
Posts: n/a
 
      12-16-2003
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.


 
Reply With Quote
 
 
 
 
Chris Hohmann
Guest
Posts: n/a
 
      12-16-2003
"Bryan Harrington" <(E-Mail Removed)> wrote in message
news:OZjkDh$(E-Mail Removed)...
> 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


 
Reply With Quote
 
Jeff Cochran
Guest
Posts: n/a
 
      12-16-2003
On Tue, 16 Dec 2003 12:16:50 -0500, "Bryan Harrington"
<(E-Mail Removed)> 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
 
Reply With Quote
 
Aaron Bertrand - MVP
Guest
Posts: n/a
 
      12-16-2003
> 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


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
VoIPCheap/Stunt/SIPDiscount/Et.al - Mobile - Top-up Expiry Period -- Campaign for Correct Expiry Period on Finarea VOIP Service Mobile Top-Ups News Reader UK VOIP 16 06-26-2006 05:03 PM
Top-Up Methods - Finarea (Voipcheap, internetcalls, etc.) et. al., VOIP Services Question - Top-up Methods News Reader UK VOIP 0 04-10-2006 02:41 PM
Error 403-Error 403-Error 403 willem joubert ASP .Net Web Services 1 02-08-2005 06:47 PM
Why does my Top DashBoard Image do not touch top of the screen? Frederic HOUDE HTML 4 08-30-2004 11:22 PM
select of select box will select multiple in another box palmiere Javascript 1 02-09-2004 01:11 PM



Advertisments