CODEWITHSUNDEEP
sqlsql-server
Crimius
Crimius

Reputation: 534

limit select based on group

What I have is 2 tables. table A looks like:

actID; jobnumber; actTypeID; completeDate
1;    2000;       3;      2012-09-04
2;    2000;       4;      2012-09-05
3;    2001;       2;      2012-09-10
4;    2001;       4;      2012-09-05
5;    2001;       5;      2012-09-05
6;    2002;       1;      2012-09-12

Table B looks like:

actTypeID;  actType;  projStatus;
1;     Pick;    Build;
2;     Bid;     Estimate;
3;     PMQC;    QC;
4;     Equipment Test;    QC;
5;     Assembly;    Build;

What I need to do is include the projStatus and actType in this:

SELECT jobnumber,
    max(completedate) completedate
FROM tableA
GROUP BY jobnumber

without increasing the number of records returned, ie get this set back:

jobnumber; actType; projStatus; completedate;
2000;  EquipmentTest; QC;       2012-09-05;
2001;  Bid;           Estimate; 2012-09-10;
2002;  Pick;          Build;    2012-09-12;

I'm on SQL server 2005 right now, soon to be 2008. I feel like I'm missing the trick to this.

Upvotes: 0

Views: 64

Answers (3)

Baral
Baral

Reputation: 3141

Try this:

select a.jobnumber, b.actType, b.projStatus, a.completedate
from 
(select jobnumber, 
       max(actTypeId) as actTypeId,
group by jobnumber) groupTableA
inner join tableA a ON a.jobNumber = groupTableA.jobNumber AND a.actTypeId = groupTableA.actTypeId
inner join tableB b ON b.actTypeId = a.actTypeId

Upvotes: 0

podiluska
podiluska

Reputation: 51494

You are looking for the ROW_NUMBER() function

select jobnumber, actType, projStatus, completedate from
(
    select *, 
        ROW_NUMBER() over (partition by jobnumber order by completedate desc) rn 
    from tablea
) v
    left join tableb
        on v.acttypeid = tableb.actTypeID
where rn = 1

Upvotes: 2

John Woo
John Woo

Reputation: 263723

Try this,

SELECT  x.[jobnumber], x.[actType], x.[projStatus], x.[completeDate]
FROM
(
SELECT a.[actID], a.[jobnumber], b.[actType], b.[projStatus], a.[completeDate],
      ROW_NUMBER() OVER 
     (PARTITiON BY jobnumber ORDER BY [completeDate] DESC ) xx
FROM table1 a
      INNER JOIN table2 b
        on a.[actTypeID]=b.[actTypeID]
) x
WHERE x.xx = 1

SQLFiddle Demo

Upvotes: 4

Related Questions