Order By messes up TOP 1 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Order By messes up TOP 1

Author

Topic

jmarshll
Starting Member

3 Posts

Posted - 2004-04-21 : 16:38:32

I'm using SQL Server 2000.

I have a basic select statement as follows:

select top 1 envelopeid
from envelopes
where queueid = somenumber
order by priority asc

However, when I run this I get not the first row, but the second. If I remove the TOP 1 and just use
SET ROWCOUNT 1, it works correctly. Also, if I remove the Order By, using the TOP 1 works correctly, but this won't work for me in all cases, so it's not a solution.

Anybody had this problem before? Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-21 : 16:42:34

So let's say you have envelopeid of 1,2,3,4. Your query should give you 1. Without the ORDER BY, it will give you the first one it finds in the table. But you can't guarantee which one it will find. So are you saying that you are getting a 2 instead with the ORDER BY? That just isn't possible.

Keep in mind that the order in which the rows were entered does not mean that that is how SQL will return the rows without an ORDER BY.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-21 : 17:07:48

[stamped on my forehead]
The order of data in a database is meangless
[/stamped on my forehead]

What ever SET ROWCOUNT gives you is arbitray (as is SELECT TOP1)

It's all about the ORDER BY

Now, if your concerned with When the data was added, then you need an ADD_TS datetime column (which the will also not prevent ties...add TieBreaker IDENTITY(1,1))

It's all just luck without the ORDER BY...I'm sure there are some interals discussion that can describe why...

but you don't need to know that to know (see above)

Brett

8-)

jmarshll
Starting Member

3 Posts

Posted - 2004-04-21 : 17:46:38

Thanks for the replies. To clarify, I should mention that this problem arises only when all the rows returned have the same Priority value.

What I have is this query...

select envelopeid
from envelopes
where queueid = somenumber
order by priority asc

returns data like this...

row1
row2
row3

When I add "TOP 1" like this...

select top 1 envelopeid
from envelopes
where queueid = somenumber
order by priority asc

I get returned this...

row2

When I run this...

set rowcount 1
select envelopeid
from envelopes
where queueid = somenumber
order by priority asc

I get this...

row1

I know the order of the data in the db does not matter, and I don't care about it. I only mentioned it because when I run this query without the "ORDER BY"...

select top 1 envelopeid
from envelopes
where queueid = somenumber

I still just happen to get the following correct order, which I know is just luck...

row1
row2
row3

Since all rows have the same Priority, adding the ORDER BY should not cause the order to change, but it does when using the TOP 1, but not when I use the SET ROWCOUNT 1.

So, there appear to be 2 issues here. First, the SET ROWCOUNT 1 query returns row1 with the ORDER BY where the TOP 1 query returns row2. Second, executing the TOP 1 query without the ORDER BY returns a different order of rows than it does with the ORDER BY, even though all rows have the same value for the order criterion. Again, ORDER BY with SET ROWCOUNT 1 returns row1, where ORDER BY with TOP 1 returns row2.

You might think it shouldn't matter which row I get as long as all rows have the same priority, but the users are seeing these envelopes listed in a queue on their screens in a certain order (row1, row2, row3), and the one on the top of the list never gets picked up to be worked until it's the last one in the queue. Functionally it's ok, but it looks weird to the users. Did I make that clear as mud? Thanks for your help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-21 : 17:56:11

Do you have a column in your table that signifies when the row was inserted? If so, then you should use that in your ORDER BY to solve your problem. If you don't, then you need to add one. You can have it default to GETDATE().

Tara

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-04-22 : 04:39:14

quote:

select top 1 envelopeid
from envelopes
where queueid = somenumber

I still just happen to get the following correct order, which I know is just luck...

row1
row2
row3

Thats impossible with top...

jmarshll
Starting Member

3 Posts

Posted - 2004-04-22 : 12:21:27

quote:
Originally posted by RickD

quote:

select top 1 envelopeid
from envelopes
where queueid = somenumber

I still just happen to get the following correct order, which I know is just luck...

row1
row2
row3

Thats impossible with top...

right...sorry to confuse things. I meant to show that query without TOP 1, which I used to show the row order retrieved by Order By, but when TOP 1 is used it is changed, and I get row2...copy and paste strikes again.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-22 : 12:28:43

Show us some data.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources