SQL Query to get maximum value based on different maximum value for multiple records
I know I can do this with a CTE or some other means, but I'm wondering if it can be done in a single select query (no subselections). I want to find the most recent crt_ts for po_nbr and then take the associated id.
Note:
The id column can't be consistent, I've 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 output:
id po_nbr
---------
6 100
18 200
source to share
SELECT
MAX(id) id,
po_nbr
FROM
temp
GROUP BY
po_nbr
To have an appropriate date, you can 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
GROUP BY
can be omitted if no two equal dates are guaranteed in the group po_nbr
.
In the last query, pointers to crt_ts
and po_nbr
will help you build the best combined index.
source to share