GROUP BY primary key, forign key composite

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 2008 Forums

Transact-SQL (2008)

GROUP BY primary key, forign key composite

Author

Topic

cornall
Posting Yak Master

148 Posts

Posted - 2011-05-31 : 12:08:47

Hi,

I have some records which can be an extension of themselves.

e.g.


id value amendmentId
1   10     NULL
2   20     NULL
3   10       1
4    5     NULL
5   39       1

When I do a select I want them to come out in the following order


id value amendmentId
1   10     NULL
3   10       1
5   39       1
2   20     NULL
4    5     NULL

Was going to do it like this is there a better way?


SELECT id
      , ISNULL(amendmentId,Id) groupId)
      ,value 
FROM TABLE
GROUP BY ISNULL(amendmentId, Id)
        ,id
        ,value

Hope this makes some sense!

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-31 : 12:22:43

select *
from tbl
order by coalesce(AmendmentID,id),id

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources