How can I SELECT rows with MAX(Column value), DISTINCT by MULTIPLE columns in SQL

Question

My table is:

id  env       date      name    PI # 
---|-----|------------|--------|-----
 1 | 10  | 04/03/2009 |   john | 399 
 2 | 11  | 04/03/2009 |  juliet| 244 
 5 | 12  | 04/03/2009 |  borat | 345
 3 | 10  | 03/03/2009 |  john  | 399
 4 | 11  | 03/03/2009 | juliet | 244
 6 | 12  | 03/03/2009 |  borat | 500
 7 | 13  | 24/12/2008 |  borat | 650
 8 | 13  | 01/01/2009 |  borat | 650

This post is slightly modified from the question below.

How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?

The difference is that I want to select each distinct environment and PI# holding the maximum value of date. For example when two rows have the same env & their PI #s are the same (rows 3 & 1 ,2 & 4, 7 & 8), I would like to return the row with the max date.

Below is the desired result.

id  env     date      name    PI # 
---|----|------------|--------|----
 1 | 10 | 04/03/2009 | john   | 399
 2 | 11 | 04/03/2009 | juliet | 244
 5 | 12 | 04/03/2009 | borat  | 345
 6 | 12 | 03/03/2009 | borat  | 500
 8 | 13 | 01/01/2009 | borat  | 650

score 1 · Answer 1 · answered Nov 20 '18 at 20:54

A typical method uses a correlated subquery:

select t.*
from t
where t.date = (select max(t2.date) from t t2 where t2.env = t.env);

Perhaps a slightly better method is:

select t.*
from t
where t.id = (select t2.id
              from t t2 
              where t2.env = t.env
              order by t2.date desc, t2.id desc
              limit 1
             );

This is slightly better because (1) id is probably a primary key, so the match is faster; and (2) if there are multiple rows on the same date, then only one row is returned.

Barbaros Özhan · Answer 2 · 2018-11-20T21:06:55.910

1

You may use the following way to get the desired results :

select *
  from tab 
 where (env,date,PI) in
(
select env, max(date) as date, PI
  from tab
 group by env, PI  
);

 id env    date     name     PI
 -- --- ---------- -------  ----
 1  10  04.03.2009  john    399
 2  11  04.03.2009  juliet  244
 5  12  04.03.2009  borat   345
 6  12  03.03.2009  borat   500
 8  13  01.01.2009  borat   650

Rextester Demo

edited Nov 20 '18 at 21:06

answered Nov 20 '18 at 20:56

Barbaros Özhan

59,113
10
31
55

score 0 · Answer 3 · answered Nov 20 '18 at 21:07

I guess this is what you want:

SELECT * FROM (SELECT DISTINCT table1.env, table1.PI FROM table1) AS q 
INNER JOIN table1 AS t ON (q.PI = t.PI) AND (q.env = t.env)
WHERE t.date = (SELECT MAX(table1.date) FROM table1 WHERE table1.env = t.env AND table1.PI=t.PI)

How can I SELECT rows with MAX(Column value), DISTINCT by MULTIPLE columns in SQL

3 Answers3

Linked