ordering table content

• WesselN

  SSC Enthusiast

  Points: 190

  More actions

  June 13, 2012 at 6:28 am

  #257592

  Hello all,

  I need to order the resultset from a query in a specific way, but I haven't got a clue how to achieve this.

  Select * from table gives me the result below:

  levelparentchildinfo

  112341237some remarks

  112341238extra remarks

  112341239more remarks

  212371240even more remarks

  212371241blablabla

  312401242bladibladibla

  Is it possible to order it this way?

  levelparentchildinfo

  112341237some remarks

  212371240even more remarks

  312401242bladibladibla

  212371241blablabla

  112341238extra remarks

  112341239more remarks

  In the second way of sorting a parent number appears directly underneath the corresponding number in the child column.

  How should the T-SQL command look to achieve this?

  Thanks,

  Wessel

• robert.gerald.taylor

  SSChampion

  Points: 13133

  More actions

  June 13, 2012 at 6:33 am

  #1500320

  Is it possible to order it this way?

  1 1234 1237 some remarks

  2 1237 1240 even more remarks

  3 1240 1242 bladibladibla

  2 1237 1241 blablabla

  1 1234 1238 extra remarks

  1 1234 1239 more remarks

  There's got to be more that you're not showing. Otherwise, how do you know that "[font="Courier New"]2 1237 1241 blablablah[/font]" isn't supposed to be the second record in the order?

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  June 13, 2012 at 6:49 am

  #1500330

  Sample data for use: -

  SELECT level, parent, child, info

  INTO #yourTable

  FROM (VALUES(1,1234,1237,'some remarks'),

  (1,1234,1238,'extra remarks'),

  (1,1234,1239,'more remarks'),

  (2,1237,1240,'even more remarks'),

  (2,1237,1241,'blablabla'),

  (3,1240,1242,'bladibladibla'))a(level, parent, child, info);

  That makes it easier for people to help you. Now, using the above sample data we can do this: -

  SELECT level, parent, child, info

  FROM (SELECT a.level, a.parent, a.child, a.info,

  ROW_NUMBER() OVER(PARTITION BY a.parent ORDER BY b.parent)

  FROM #yourTable a

  OUTER APPLY (SELECT level, parent, child, info

  FROM #yourTable

  WHERE a.parent = child) b

  )innerQ(level, parent, child, info, pos)

  ORDER BY pos;

  Which produces: -

  level parent child info

  ----------- ----------- ----------- -----------------

  1 1234 1237 some remarks

  2 1237 1240 even more remarks

  3 1240 1242 bladibladibla

  2 1237 1241 blablabla

  1 1234 1238 extra remarks

  1 1234 1239 more remarks

  ---

  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/

  ---

• WesselN

  SSC Enthusiast

  Points: 190

  More actions

  June 13, 2012 at 6:49 am

  #1500331

  You are correct, I overlooked one column:

  levelparentchildposinfo

  1123412371some remarks

  1123412382extra remarks

  1123412393more remarks

  2123712401even more remarks

  2123712412blablabla

  3124012421bladibladibla

  should look like:

  levelparentchildposinfo

  1123412371some remarks

  2123712401even more remarks

  3124012421bladibladibla

  2123712412blablabla

  1123412382extra remarks

  1123412393more remarks

  The position column isn't necessarily sorted ascending, because it can happen that

  3124012421bladibladibla

  is a level below parent 1241 instead of 1240.

  Hope this clears it up.

• WesselN

  SSC Enthusiast

  Points: 190

  More actions

  June 14, 2012 at 2:20 am

  #1500824

  Thanks for the replies.

  They helped me, but its still a difficult topic for me.