ORDER BY Help. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

ORDER BY Help.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2014-02-11 : 13:06:05

I need to order data by the desired output below but can't get it to work now.

Any help is appreciated. Please see the desired output below. Basically,
I need to Sort VSQL5, 6, 7,.... 10, 11,

SQL 2012

Thank you.

IF OBJECT_ID('Tempdb.dbo.#Test', 'u') IS NOT NULL
DROP TABLE #Test
GO

CREATE TABLE #Test
(
Srvname VARCHAR(20) NULL
)
GO

INSERT #test VALUES ('All'), ('VCUS3RCA'), ('VCUS4RCA'), ('VCUS5RCA'),('VCUS6RCA'),
('VSQL10'), ('VSQL11'), ('VSQL12'), ('VSQL13'), ('VSQL14'), ('VSQL15'),
('VSQL16'), ('VSQL17'),('VSQL18'), ('VSQL4'), ('VSQL5'), ('VSQL6'), ('VSQL7'), ('VSQL8'),
('VSQL9'), ('VSQL3');

SELECT * FROM #Test;

-- Desired Results:
Srvname
--------------------
Srvname
--------------------
All
VCUS3RCA
VCUS4RCA
VCUS5RCA
VCUS6RCA

VSQL3
VSQL4
VSQL5
VSQL6
VSQL7
VSQL8
VSQL9
VSQL10
VSQL11
VSQL12
VSQL13
VSQL14
VSQL15
VSQL16
VSQL17
VSQL18

--Testing
SELECT *, Row_Number() over(order by Srvname ASC) FROM #Test
ORDER BY Row_Number() over(order by Srvname ASC)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-02-11 : 14:41:04

That is ugly. They would sort naturally if the [SrvName] number suffixes had leading zeros like VSQL002.
This expression does that. It's not pretty but there's no "good" way.


order by case 
       when SrvName like 'VSQL%' then 'VSQL' + replace(str(replace(Srvname, 'VSQL',''), 3),' ','0')
       else SrvName
       end

Actually the right way would be to create a mapping table that has SrvName and SortOrder. Join by SrvName and Order by SortOrder.

Be One with the Optimizer
TG

NguyenL71
Posting Yak Master

228 Posts

Posted - 2014-02-11 : 17:32:14

Thank you for suggestions.

quote:
Originally posted by TG

That is ugly. They would sort naturally if the [SrvName] number suffixes had leading zeros like VSQL002.
This expression does that. It's not pretty but there's no "good" way.

order by case
when SrvName like 'VSQL%' then 'VSQL' + replace(str(replace(Srvname, 'VSQL',''), 3),' ','0')
else SrvName
end

Actually the right way would be to create a mapping table that has SrvName and SortOrder. Join by SrvName and Order by SortOrder.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources