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 '12 at 02:30 PM in Default

nayana6 gravatar image

nayana6
20 2

is it in a table? how do you decide which 8 96 should be first?
Sep 11 '12 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 '12 at 03:28 PM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x1816

asked: Sep 11 '12 at 02:30 PM

Seen: 270 times

Last Updated: Sep 11 '12 at 07:34 PM