How to order the rows?

• Charmer

  SSChampion

  Points: 14903

  More actions

  October 12, 2012 at 6:19 am

  #283257

  Hi Friends,

  One of my colleague asked me a wired scenerio that the output he is looking for...

  I have no idea how do we get it or even whether possible or not? So i have come to get your sugeestions ...please gimme your ideas friends...

  IF OBJECT_ID('TC','U') IS NOT NULL BEGIN

  DROP TABLE TC

  END

  GO

  CREATE TABLE TC(

  [ProductID] [INT] NOT NULL,

  [SeqNo] int null

  ) ON [PRIMARY]

  GO

  INSERT INTO TC(ProductID,SeqNo)

  SELECT 1,0 UNION ALL

  SELECT 1,1 UNION ALL

  SELECT 1,2 UNION ALL

  SELECT 2,0 UNION ALL

  SELECT 2,null UNION ALL

  SELECT 2,1 UNION ALL

  SELECT 3,1 UNION ALL

  SELECT 4,null UNION ALL

  SELECT 5,2

  I have attached the screenshot of expecting output....

  Is it possible to do such ascending order not considering the null values...? Null values must come at last to the respective ProductID...

  Please let me know if myquestion is not clear enough..

  Thanks,
  Charmer

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  October 12, 2012 at 6:26 am

  #1548589

  Same as the last post I made, still on lunch and waiting in the car so this is untested and written on my phone. Forgive any syntax errors.

  Here are two options: -

  -- Option 1

  SELECT ProductID, SeqNo

  FROM TC

  ORDER BY ProductID, ISNULL(SeqNo,2147483647);

  -- Option 2

  SELECT ProductID, SeqNo

  FROM TC

  ORDER BY ProductID, CASE WHEN SeqNo IS NULL THEN 1 ELSE 0 END, SeqNo;

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• Charmer

  SSChampion

  Points: 14903

  More actions

  October 12, 2012 at 6:54 am

  #1548605

  Cadavre (10/12/2012)

  ---

  Same as the last post I made, still on lunch and waiting in the car so this is untested and written on my phone. Forgive any syntax errors.

  Here are two options: -

  -- Option 1

  SELECT ProductID, SeqNo

  FROM TC

  ORDER BY ProductID, ISNULL(SeqNo,2147483647);

  -- Option 2

  SELECT ProductID, SeqNo

  FROM TC

  ORDER BY ProductID, CASE WHEN SeqNo IS NULL THEN 1 ELSE 0 END, SeqNo;

  Yes...It is working..

  Thank you Cadare..

  Thanks,
  Charmer