Order By messes up TOP 1

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.

Not sure why you need the TOP when you're only looking for 1 row? Is it a prob to just use filtering in your where clause to get the row you want? Just curious.

define "correctly"

there is no "second row" without an ORDER BY

there is no concept of position in a relational table, only in a result set after the ORDER BY has been applied

if you then choose TOP 1, then that's what you get

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 order, which I know is just dumb 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, as they come out of the db), 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.

Jim

There is no order to change. All your reasoning is still based on the assumption that the rows has a certain order in the table.

Add more columns to the order by clause to distinguish between rows with the same priority.