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

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

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

Regards,
Arunkumar.Dharuman





On May 15, 5:54 pm, "Plamen Ratchev" <Pla...@SQLStudio.com> wrote:
> 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