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.

Answer 1

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)

Answer 2

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