Multiple order by in a result set

Question

Post reply

Multiple order by in a result set

santhosh_ms3

Default port

Points: 1482
More actions
March 29, 2013 at 3:54 am

#270483

I have a specific requirment where a table contains a column called ‘Empno’ with the values 12, 34, 3, 78, 65, 109, 8. I want first three values(12, 34, 3) should print as it is. rest of the values should come in ascending order. expected out come should be as 12, 34, 3, 8,65,78,109.
I tried using rownumber() with case statement in order by clause. but couldnt help?
any ideas please?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

deepkt SSC Eights! Points: 951 More actions · Answer 1

Try this:

SELECT TOP 3 EMPID FROM EmpTable

UNION ALL

(

SELECT EMPID FROM EmpTable

EXCEPT

SELECT TOP 3 EMPID FROM EmpTable

)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

deepkt (3/29/2013)
Try this:
SELECT TOP 3 EMPID FROM EmpTable
UNION ALL
(
SELECT EMPID FROM EmpTable
EXCEPT
SELECT TOP 3 EMPID FROM EmpTable
)

Top without order by = inconsistent results. With that, SQL will give you whichever 3 rows it gets first, with no regard to which 3 it got you last time.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

santhosh_ms3 (3/29/2013)
I have a specific requirment where a table contains a column called ‘Empno’ with the values 12, 34, 3, 78, 65, 109, 8. I want first three values(12, 34, 3) should print as it is.

First three values by what criteria? Rows in a table have no order, so you can't say anything about which 3 rows are the 'first' in the table.

To make it easier, please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Erin Ramsay SSCrazy Eights Points: 9728 More actions · Answer 4

You mean something like this?

select empno

from

(

select 12 empno

union

select 34 empno

union

select 3 empno

union

select 78 empno

union

select 65 empno

union

select 109 empno

union

select 8 empno

) source

order by (case when empno = 12 then 1

when empno = 34 then 2

when empno = 3 then 3

else 4

end) asc

santhosh_ms3 Default port Points: 1482 More actions · Answer 5

Thanks All for your responses.

i tried this and its working... but not sure how far its accurate as its based on physical location.

my requirment is to get top 3 the records in a order which they got inserted and rest all in a sorted order.

WITH CTE AS (

SELECT ROW_NUMBER() OVER ( ORDER BY SYS.PHYSLOCFORMATTER (%%PHYLOC%%) ) AS RN, empid FROM emptable)

SELECT empid FROM CTE ORDER BY CASE WHEN RN > 3 THEN empid END ASC

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

santhosh_ms3 (3/29/2013)
my requirment is to get top 3 the records in a order which they got inserted and rest all in a sorted order.

The *only* way you can do that is with a column that records the insert order, an identity or a date.

A SQL table does not have any defined order upon it, there is no code that is going to consistency and reliably give you the insert order for rows (and that includes the physical location formatter that you tried to use, it may work in your testing, it is not going to work in general)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

santhosh_ms3 (3/29/2013)
Thanks All for your responses.
i tried this and its working... but not sure how far its accurate as its based on physical location.
my requirment is to get top 3 the records in a order which they got inserted and rest all in a sorted order.
WITH CTE AS (
SELECT ROW_NUMBER() OVER ( ORDER BY SYS.PHYSLOCFORMATTER (%%PHYLOC%%) ) AS RN, empid FROM emptable)
SELECT empid FROM CTE ORDER BY CASE WHEN RN > 3 THEN empid END ASC

You really need to take Gail's advice on this. Unless you have a datetime, IDENTITY, or a specific sort order column, you will get burned someday in the future.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

santhosh_ms3 Default port Points: 1482 More actions · Answer 8

Yes.. Thank you. But Is there any way to manipulate the records through case statements. I still believe that there could be a way to control it.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

santhosh_ms3 (3/31/2013)
But Is there any way to manipulate the records through case statements. I still believe that there could be a way to control it.

To obtain the insert order without a column that records the insert order? No, there's no way to reliably get that in SQL Server. If you want to know the order inserted, you need a column that records that, like a datetime column defaulting to GETDATE().

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass