May 26, 2010 at 8:19 am
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
-------- --------------
3LIVERPOOL
2SEFTON
6ABERDEEN
4AYR
5GLASGOW
1KNOWLSEY
7LONDON
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
May 26, 2010 at 8:43 am
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
May 26, 2010 at 8:48 am
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...
May 26, 2010 at 9:26 am
Thank you, I'll give both a try tomorrow and get back to you.
MCITP SQL 2005, MCSA SQL 2012
May 27, 2010 at 6:46 am
The second suggestion gave the better performance of the two with exactly the results I was looking for.
Thank You
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