Hi,
I have a query which presents results sorted using the SQL "ORDER BY...
LIMIT". Now my question is... if the column that the order refers to
has some repeated data, will the order of results always be the same?
For instance, say I have a table as follows:
create table example {id serial primary key, stuff int};
Now suppose I the table is populated as follows:
ID STUFF
1 10
2 11
3 11
4 12
Now if i try SELECT ID,STUFF FROM EXAMPLE ORDER BY STUFF; I might expect
ID STUFF
1 10
2 11
3 11
4 12
but possibly could get
ID STUFF
1 10
3 11
2 11
4 12
... Depending on the implementation. Whichever result I get, can I
always expect the output in the same way? Or should I ensure this by
sorting by the ID aswell
e.g. SELECT ID,STUFF FROM EXAMPLE ORDER BY STUFF, ID; - which would
always give the same order.
In case you are interested, the application for this problem is the
presentation of partial results using LIMIT (as per search results
20,21...30) - for instance could the following present different results
to the same client...
SELECT ID,STUFF FROM EXAMPLE WHERE STUFF=11 ORDER BY STUFF LIMIT 0, 1;
Could give the following on one transaction
2 11
And the following on another transaction
3 11
I am not bothered about which way the results are presented and I am
aware that the order would be implementaion specific, but obviously the
results presented should always be the same for my app.
Regards,
Howard Cole
www.selestial.com
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
