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.
| Author |
Topic |
|
Rangi
Starting Member
18 Posts |
 Posted - 2002-12-04 : 11:50:57
|
| I have a table like this:pk_no image_no---------------1 5222 5223 571... ...The pk_no is unique (autonumber) but the image_no values are not all unique.How can I select distinct image_no ordered by pk_no? If I try: SELECT DISTINCT image_no FROM mytable ORDER BY pk_noSQL Server 2000 tells me: "ORDER BY items must appear in the select list if SELECT DISTINCT is specified."However if I include the pk_no in the select list, I can't get distinct image_no values.If I just use SELECT DISTINCT image_no FROM mytableThen SQL Server 2000 automatically orders the results by image_no. Maybe there is a way to turn off this automatic ordering? Or what about a way to order the rows in the order they were inserted in?Any help would be very much appreciated!Rangi |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-12-04 : 12:02:09
|
You are asking SQL to make a decision for you and it doesn't like to think for itself ...Consider this as your data ...pk_no image_no --------------- 1 522 2 571 3 522 How would you suggest SQL return the rows? 522 first or 571?What you need to do is make the decision for SQL ...select min(pk_no) as min_pk_no, image_nofrom mytablegroup by image_noorder by min(pk_no) Jay White{0} |
 |
|
|
Rangi
Starting Member
18 Posts |
Posted - 2002-12-04 : 12:19:13
|
| Jay,Thanks very much, that works great!Rangi |
|
|
|
|
|
|
Can I turn off default ordering?