sorting table

[Oonz]

Hi friends,
ID LASTNAME
--------+----------------------------
2 FFF
1 XXX
0 CCC
1 DDD
2 BBB
0 EEE
0 GGG
3 III
3 HHH
4 ZZZ
4 ZZZ
please help me in sorting this table. the table should be sorted based
on LASTNAME, and then the ID (only non-zero id should be sorted).
ID LASTNAME
--------+----------------------------
2 BBB
2 FFF
0 CCC
1 DDD
1 XXX
0 EEE
0 GGG
3 HHH
3 III
4 XYZ
4 ZZZ

should be sorted based on last name and should be grouped with the
same ID, except for the ID as 0

[Plamen Ratchev]

Please don't multipost, rather crosspost. Replied in
microsoft.public.sqlserver.server:

SELECT F.ID,
F.LASTNAME
FROM Foobar AS F
LEFT OUTER JOIN
(
SELECT ID,
MIN(LASTNAME) AS GRP
FROM Foobar
WHERE ID 0
GROUP BY ID
) AS G
ON F.ID = G.ID
ORDER BY COALESCE(G.GRP, F.LASTNAME),
F.ID,
F.LASTNAME

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[Oonz]

Hi Plamen,
It was really great.Thanks for your immediate reply.

Regards,
Arunkumar.Dharuman

On May 15, 5:54 pm, "Plamen Ratchev" <[e-mail]:

Please don't multipost, rather crosspost. Replied in
microsoft.public.sqlserver.server:

SELECT F.ID,
F.LASTNAME
FROM Foobar AS F
LEFT OUTER JOIN
(
SELECT ID,
MIN(LASTNAME) AS GRP
FROM Foobar
WHERE ID 0
GROUP BY ID
) AS G
ON F.ID = G.ID
ORDER BY COALESCE(G.GRP, F.LASTNAME),
F.ID,
F.LASTNAME

HTH,

Plamen Ratchevhttp://www.SQLStudio.com