top - 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 2005 Forums

Transact-SQL (2005)

top

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-11-30 : 08:11:32

my query is not working
but i have a table called children with childid,childname,familyid

and a table called family with familyid,familyname

how can i get a list of the first child in every family
(order by id)

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-11-30 : 08:16:01

[code]SELECT *
FROM (
SELECT *, RowNum = ROW_NUMBER() OVER (PARTITION BY a.FamilyID ORDER BY ChildID)
FROM children a
INNER JOIN family b
ON a.FamilyID = b.FamilyID
) AS dt
WHERE dt.RowNum = 1[/code]

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-30 : 08:19:45

And this is for Top-N child
http://beyondrelational.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx

Madhivanan

Failing to plan is Planning to fail

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-11-30 : 08:19:49

The column 'familyid' was specified multiple times for 'dt'.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-30 : 08:21:25

quote:
Originally posted by esthera

The column 'familyid' was specified multiple times for 'dt'.

This is a strong reason to explicitely specify the column names than *

Madhivanan

Failing to plan is Planning to fail

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-11-30 : 08:58:08

Feel free to think for yourself as well. After 1150 posts on this site I would expect you to be able to do some debugging for yourself...

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2010-11-30 : 12:50:15

An alternate approach that I like (for no particular reason, probably because intuitively I think it may generate a better query plan) is using cross/outer apply as follows (not tested):

select
   familyid,
   familyname,
   firstchildname
from
   family f
   cross apply
   (
      select top (1)
         childname as firstchildname
      from
         children c
      where
         c.familyid = f.familyid
      order by
         childid
    ) fc

If you want to return a row for families with no children, use outer apply instead of cross apply.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources