Order by

• pzmrcd

  Default port

  Points: 1419

  More actions

  July 20, 2005 at 9:47 am

  #165012

  Hi

  If I have

  Select * from T1 order By NewID()

  can I under special conditions bring a specific row (say id = 100) to the top without changing the query .

  Is there a work around for this.

   

  Thanks

• Max Mulawa

  SSC Enthusiast

  Points: 174

  More actions

  July 20, 2005 at 10:06 am

  #575856

  Hi,

  Select * from T1 WHERE id = 100

  UNION ALL

  Select * from T1 WHERE id 100 order By NewID()

  I hope this will be useful

  Max

• Jesper-244176

  SSCertifiable

  Points: 7082

  More actions

  July 21, 2005 at 1:41 am

  #575986

  Another possibility is to sort by a case statement, like the following:

  order by case when id = 100 then -1 else id end

• David Burrows

  SSC Guru

  Points: 65111

  More actions

  July 21, 2005 at 4:20 am

  #576017

  This will not work for 2 reasons

  1. newid() has to be in the select for the order by to work (you will get an error)

  2. order by affects the whole query and therefore even if the above worked it would not produce the right result

  Jesper is on the right track and if I understand your question correctly this is what you require

  Select * from T1

  order By CASE WHEN itemid=100 THEN -1 ELSE 1 END, NewID()

  Far away is close at hand in the images of elsewhere.
  Anon.

• Anthony Zackin

  SSC Enthusiast

  Points: 154

  More actions

  July 21, 2005 at 12:31 pm

  #576197

  Here's another possible solution although it does create an extra useless column.  However this technique might be useful for other applications.

  use

  pubs

  go

  select 0 as " ",* from titles where titles.royalty = 10

  union

  all

  select

  1,* from titles where titles.royalty <> 10

  order

  by 1,royalty desc

  go