ordering with 'other'

[mjd3000]

I have a result set like this :

Other 34%
Dan 24%
Ken 17%
Claire 16%
Helen 9%

How do I other this so that the Other always shows as the last row, even if it isn't the lowest percentage?

 

[bborissov]

DECLARE @Test TABLE (name varchar(20), Prc int)
INSERT INTO @Test VALUES ('Other',  34)
INSERT INTO @Test VALUES ('Dan'   , 24)
INSERT INTO @Test VALUES ('Ken'    ,17)
INSERT INTO @Test VALUES ('Claire' ,16)
INSERT INTO @Test VALUES ('Helen'  , 9)


SELECT Name, Prc
FROM @Test
ORDER BY Prc + CASE WHEN name = 'Other' Then 999 ELSE 0 END

or

DECLARE @Test TABLE (name varchar(20), Prc int)
INSERT INTO @Test VALUES ('Other',  34)
INSERT INTO @Test VALUES ('Dan'   , 24)
INSERT INTO @Test VALUES ('Ken'    ,17)
INSERT INTO @Test VALUES ('Claire' ,16)
INSERT INTO @Test VALUES ('Helen'  , 9)


SELECT Name, Prc
FROM @Test
ORDER BY CASE WHEN name = 'Other' Then 1 ELSE 0 END, Prc

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.