Select the maximum value for each (Col1,Col2)

Question

Lets assume the following table:

Name             SubName        Message    Time
USA             MA              M1         1
USA             NY              M2         2
USA             WA              M3         3
USA             MA              M4         4
USA             WA              M5         5
USA             NY              M6         6
FIN             HEL             M7         7
FIN             TAM             M8         8
FIN             HEL             M9         9

I want a SQL query which will return the following:

Name             SubName        Message    Time
FIN              HEL            M9         9
FIN              TAM            M8         8
USA              NY             M6         6
USA              WA             M5         5
USA              MA             M4         4

so a ORDER BY time DESC, which is grouped by distinct name and sub-grouped by distinct subname.

Is this possible? I'm looking for a solution that is not DBMS-specific - something that can run in most all DBMS.

lets say the db is irrelevant. we want to come up with a sql which should work any where. — Ahmad Mushtaq, Aug 10 '16 at 13:13
@AhmadMushtaq, if you want a query that runs on (almost) any dbms, stick to Core ANSI SQL. (Avoid row_number etc.) — jarlh, Aug 10 '16 at 13:40

sagi · Answer 1 · 2016-08-11T21:55:37.123

You didn't tag your RDBMS, so for ANSI-SQL which should work on most RDBMS's you can use ROW_NUMBER() :

SELECT s.* FROM (
    SELECT t.*,
           ROW_NUMBER() OVER(PARTITION BY t.name,t.subname ORDER BY t.time DESC) as rnk
    FROM YourTable
    ) s
WHERE s.rnk = 1
ORDER BY s.time DESC,s.name

EDIT: Here is an answer with Core ANSI SQL , that should work on ANY database you can use NOT EXISTS() :

SELECT * FROM YourTable t
WHERE NOT EXISTS(SELECT 1 FROM YourTable s
                 WHERE t.name = s.name and t.subname = s.subname
                   AND s.time > t.time)

score 5 · Answer 2 · answered Aug 10 '16 at 13:09

SELECT * FROM 
#TEMP T1 WHERE TIME=
(SELECT MAX(TIME) FROM #TEMP T2
WHERE T1.NAME=T2.NAME AND T2.SUBNAME=T1.SUBNAME)

Output:

Name    SubName Message Time
USA       WA    M5       5
USA       NY    M6       6
USA       MA    M4       4
FIN       TAM   M8       8
FIN       HEl   M9       9

jarlh · Answer 3 · 2016-08-10T13:43:02.770

And if your dbms doesn't support ANSI SQL's extension T611, Elementary OLAP operations:

select t1.*
from tablename t1
  join (select name, subname, max(Time) maxtime
        from tablename
        group by name, subname)
   on t1.name = t2.name and
      t1.subname = t2.subname and
      t1.time= t2.maxtime
order by name, subname, time desc

The following feature outside Core SQL-2003 is used: F591, "Derived tables"

Note that ANSI SQL has time as a reserved word, so you may need delimit it as "time".

score 3 · Answer 4 · answered Aug 10 '16 at 13:01

You can do this in two steps. First choose the best for each name/subname combination. Then order them. In most databases, you can use row_number() for the first piece:

select t.*
from (select t.*,
             row_number() over (partition by t.name, t.subname order by time desc) as seqnum
      from t
     ) t
order by time desc;

Grace · Answer 5 · 2016-08-10T13:33:43.497

3

may use ROW_NUMBER() and WHERE s.rank = 1 to get the first record in each group made by partition by:

SELECT s.Name,s.SubName,s.Message, s.Time
FROM (
    SELECT t.*,
           ROW_NUMBER() OVER(PARTITION BY t.Name, t.SubName ORDER BY t.Time DESC) as rank
    FROM YourTable t) s
WHERE s.rank = 1
ORDER BY s.Time DESC

edited Aug 10 '16 at 13:33

answered Aug 10 '16 at 13:01

Grace

876
1
11
21

Although this code may help to solve the problem, it doesn't explain _why_ and/or _how_ it answers the question. Providing this additional context would significantly improve its long-term value. Please [edit] your answer to add explanation, including what limitations and assumptions apply. – Toby Speight Aug 10 '16 at 13:22

score 1 · Answer 6 · answered Aug 10 '16 at 17:55

I have not tested this but I think this should work:

select
    t1.name as name,
    t1.subname as subname,
    (select max(t2.message) from table t2 where t2.name = t1.name and t2.subname = t1.subname) as message,
    (select max(t2.time) from table t2 where t2.name = t1.name and t2.subname = t1.subname) as time
from
    table t1
group by
    t1.name,
    t1.subname
order by
    t1.name

Select the maximum value for each (Col1,Col2)

6 Answers6