views:

454

answers:

5

I know I can do this with a CTE or another means but I am wondering if this can be done in one select query (no sub-selects). I want to find the most recent crt_ts for the po_nbr and then take the associated id.


Note:

The id column is not guaranteed to be sequential, I simplified it for this question.


Code:

create table temp
(
 id int,
 po_nbr int,
 crt_ts datetime
)

insert into temp values (20, 100, '09/01/2009')
insert into temp values (3, 100, '09/03/2009')
insert into temp values (5, 100, '09/05/2009')
insert into temp values (6, 100, '09/07/2009')
insert into temp values (4, 200, '08/01/2009')
insert into temp values (29, 200, '08/03/2009')
insert into temp values (12, 200, '08/05/2009')
insert into temp values (18, 200, '08/07/2009')


Desired result:

id  po_nbr
---------
6   100
18  200
+3  A: 
SELECT
  MAX(id) id,
  po_nbr
FROM
  temp
GROUP BY
  po_nbr

To have the associated date, you could do (beware, this implies a sequential id):

SELECT
  temp.id,
  temp.po_nbr,
  temp.crt_ts
FROM
  temp
  INNER JOIN (
    SELECT MAX(id) id FROM temp GROUP BY po_nbr
  ) latest ON latest.id = temp.id

Without a sequential id, it would be:

SELECT
  MAX(temp.id) id,
  temp.po_nbr,
  temp.crt_ts
FROM
  temp INNER JOIN (
    SELECT   MAX(crt_ts) crt_ts, po_nbr 
    FROM     temp i
    GROUP BY po_nbr
  ) latest ON latest.crt_ts = temp.crt_ts AND latest.po_nbr = temp.po_nbr
GROUP BY
  temp.po_nbr,
  temp.crt_ts

The GROUP BY can be left out if there are guaranteed to be no two equal dates per po_nbr group.

Indexes on crt_ts and po_nbr help in the last query, creating one combined index would be best.

Tomalak
Beaten by 52 seconds :-)
RB
there is no guarantee that the ID column is sequential. I dont necessarily want the max(id)...I want the id associated with the max(crt_ts) grouped by po_nbr.
thomas
See third version.
Tomalak
using ROW_NUMBER(), like in my answer, has almost half the TotalSubtreeCost as your GROUP BY method (3rd try) when using _SET SHOWPLAN_ALL ON_
KM
Probably, but I tend to write SQL that runs on SQL Server 2000 as well, because that's what we have in production here. :-\
Tomalak
+1  A: 
SELECT 
  MAX(temp.id) AS id, det.po_nbr
FROM
  temp
  INNER JOIN (SELECT po_nbr, MAX(crt_ts) AS maxcrt_ts FROM temp GROUP BY po_nbr) AS det ON temp.po_nbr = det.po_nbr AND temp.crt_ts = maxcrt_ts
GROUP BY
  det.po_nbr
Locksfree
+2  A: 

try:

SELECT
    id,po_nbr --crt_ts --can show the date if you want
    FROM (SELECT 
              id,po_nbr,crt_ts
              ,ROW_NUMBER() OVER(partition BY po_nbr ORDER BY po_nbr,crt_ts DESC) AS RankValue
              FROM temp
         ) dt
    WHERE RankValue=1
KM
+1  A: 
select t.id, tm.po_nbr
from  temp t
inner join (
    select po_nbr, max(crt_ts) as max_ts
    from temp 
    group by po_nbr
) tm on t.po_nbr = tm.po_nbr and t.crt_ts = max_ts
RedFilter
A: 

select max(id),po_nbr from tempbarry group by po_nbr

Barry