none
How to select only the first record from a group

    Question

  • I have a question about selecting only the first record in a group.   Example:  I have table A with primary key = 999.    Table B has multiple records with primary key = 999.  How can I match Table A 999 with the first occurrence of 999 in Table B, and then extract other field data (such as street address) from the Table B record.   I have tried using the Count() function, but it seems that I can only do this using cursors. 

    Thanks,

    BobD

    Monday, September 18, 2006 6:01 PM

All replies

  • hi

    take a look at this with northwind database

    use northwind
    select * from orders
    select * from [order details]

    now here's the query to simulate your needs

    use northwind

    select a.orderid,a.productid, od2.*  from
    (
    select o.orderid,
    min(productid)as productid
    from orders o join [order details] od
    on o.orderid=od.orderid
    group by o.orderid)
    as a
    join
    [order details] od2
    on a.orderid=od2.orderid and a.productid=od2.productid

    Monday, September 18, 2006 6:28 PM
  • SELECT a.myID, a.ColA, b.id, b.ColB FROM Table_A AS a INNER JOIN (SELECT id, myID, COlB, row_number() OVER (PARTITION BY myID ORDER BY id) as RowNum

    FROM Table_B) AS b ON a.myID=b.myID

    WHERE b.RowNum=1

    Monday, September 18, 2006 7:37 PM