locked
RowCount RRS feed

  • Question

  • I have  a query that I would like to pull some information from.  Currently there are many records listed for each parent and I have it sorted so I would like to be able to retrieve just the top record for each parent.  I know that rowcount can pull the first record but is there a way to have it pull the first record for each parent ID?

    Monday, July 21, 2008 12:43 PM

Answers

  • You can use a ranking function like row_number or the "cross apply" operator.

     

    Example:

     

    use northwind

    go

     

    with r_set

    as

    (

    select

    c.customerid, oh.orderid, oh.orderdate,

    row_number() over(partition by c.customerid order by oh.orderdate, oh.orderid) as rn

    from

    dbo.customers as c left outer join dbo.orders as oh

    on c.customerid = oh.customerid

    )

    select *

    from r_set

    where rn = 1

    order by customerid

    GO

     

    -- or

     

    select c.customerid, o.orderid, o.orderdate

    from

    dbo.customers as c

    outer apply

    (

    select top 1 oh.orderid, oh.orderdate

    from dbo.orders as oh

    where oh.customerid = c.customerid

    order by oh.orderdate, oh.orderid

    ) as o

    GO

     

     

    AMB

    Monday, July 21, 2008 1:09 PM