Ordering Results

Question

Post reply

Ordering Results

RTaylor2208

SSChampion

Points: 13296
More actions
May 26, 2010 at 8:19 am

#219363

Hi

I have a requirement to return data from a single table in a set order for the first 3 rows then alaphabetically for the results of the rows:

Here is some code to create a test table and data:

-- Create Test Table

IF EXISTS (SELECT 1 FROM sysobjects where name = 'tblTest' and xtype = 'U')

DROP TABLE tblTest

CREATE TABLE tblTest (

ID INT IDENTITY (1,1),

Location varchar(max))

INSERT INTO tblTest

SELECT 'KNOWLSEY' UNION ALL

SELECT 'SEFTON' UNION ALL

SELECT 'LIVERPOOL' UNION ALL

SELECT 'AYR' UNION ALL

SELECT 'GLASGOW' UNION ALL

SELECT 'ABERDEEN' UNION ALL

SELECT 'LONDON'

The result set I am looking for is:

HB_ID LOCATION

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

3 LIVERPOOL

2 SEFTON

6 ABERDEEN

4 AYR

5 GLASGOW

1 KNOWLSEY

7 LONDON

The code I have came up with which is not particulary efficient is below any suggestions on improvements would be appreciated.

DECLARE @tblOrder TABLE (ORDID INT IDENTITY(1,1), ID INT, LOCATION VARCHAR(100))

INSERT INTO @tblOrder (ID, LOCATION)

SELECT ID, LOCATION FROM tblTest WHERE LOCATION = 'LIVERPOOL'

INSERT INTO @tblOrder (ID, LOCATION)

SELECT ID, LOCATION FROM tblTest WHERE LOCATION = 'SEFTON'

INSERT INTO @tblOrder (ID, LOCATION)

SELECT ID, LOCATION FROM tblTest WHERE LOCATION = 'KNOWSLEY'

INSERT INTO @tblOrder (ID, LOCATION)

SELECT ID, LOCATION FROM tblTest

WHERE LOCATION NOT IN ('LIVERPOOL', 'SEFTON','KNOWSLEY')

ORDER BY LOCATION

SELECT ID, LOCATION FROM @tblOrder

Thanks in advance

MCITP SQL 2005, MCSA SQL 2012

Viewing 5 posts - 1 through 4 (of 4 total)

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

Try this:



select id, location

from tblTest t

left join (select  1 oid,'LIVERPOOL' o union all select 2, 'SEFTON' union all select 3, 'KNOWLSEY') ot

on ot.o =  t.Location

order by ISNULL(ot.oid,10), t.location

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

Another version (without sub-select & join):



select id, location

from tblTest

order by

(case location when 'LIVERPOOL' then 1

               when 'SEFTON'    then 2

               when 'KNOWLSEY'  then 3

               else 4

 end)

,location

I guess this one is faster...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]