x

sorting question

I have data like this

 col 1 col2
 8 96
 8 96
 8 96
 8 97
 8 97
 8 97
 8 98
 8 98
 8 98
 

I want it sorted this way using sql server.

 col1 col2
 8 96
 8 97
 8 98
 8 96
 8 97
 8 98
 8 96
 8 97
 8 98
 

Any thoughts?

more ▼

asked Sep 11, 2012 at 02:30 PM in Default

avatar image

nayana6
20 1 3

is it in a table? how do you decide which 8 96 should be first?

Sep 11, 2012 at 02:38 PM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

As highlighted by Fatherjack, there are quite a few aspects to consider before answering the question.

But for the simplest scenario this could be a way

 SELECT * FROM
 (
 SELECT  [firstcol]
 ,       [secondcol]
 , ROW_NUMBER() OVER (PARTITION BY secondcol ORDER BY firstcol) rownum
 FROM
 (
 SELECT 8 firstcol, 96 secondcol
 UNION ALL SELECT 8, 96
 UNION ALL SELECT 8, 96
 UNION ALL SELECT 8, 97
 UNION ALL SELECT 8, 97
 UNION ALL SELECT 8, 97
 UNION ALL SELECT 8, 98
 UNION ALL SELECT 8, 98
 UNION ALL SELECT 8, 98
 )A
 ) B 
 ORDER BY [rownum],[secondcol]
more ▼

answered Sep 11, 2012 at 03:28 PM

avatar image

Usman Butt
13.9k 6 13 21

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2091

asked: Sep 11, 2012 at 02:30 PM

Seen: 433 times

Last Updated: Sep 11, 2012 at 07:34 PM

Copyright 2016 Redgate Software. Privacy Policy