CODEWITHSUNDEEP
sqlgreatest-n-per-group
Lostinthegame
Lostinthegame

Reputation: 127

SQL Query to select each row with max value per group

I'm very new to SQL and this one has me stumpted. Can you help me out with this query?

I have the following 2 tables:

TABLE 1: IssueTable

Id   | RunId | Value
---
1    | 1     | 10  
2    | 1     | 20  
3    | 1     | 30  
4    | 2     | 40  
5    | 2     | 50  
6    | 3     | 60 
7    | 4     | 70 
8    | 5     | 80 
9    | 6     | 90

TABLE 2: RunTable

RunId     | EnvironmentId
---
1         | 1
2         | 3
3         | 1
4         | 2
5         | 4
6         | 2

I need the IssueTable rows that represent the Max RunId grouped by the EnvironmentId in the RunTable. The result I would need from the tables is:

EXPECTED RESULT:

Id   | RunId | Value | EnvironmentId
---
4    | 2     | 40    | 3
5    | 2     | 50    | 3
6    | 3     | 60    | 1
8    | 5     | 80    | 4
9    | 6     | 90    | 2

So only the rows with the most recent/highest RunId from the RunTable per EnvironmentId. For example, for the EnvironmentId of "1", I only want rows that contain a RunId of "3" because the most recent RunId on EnvironmentId "1" from the RunTable is "3". Likewise, the most recent run for EnvironementId "2" was RunId "6"

Upvotes: 4

Views: 5179

Answers (2)

LukStorms
LukStorms

Reputation: 29677

These days one can use the analytical functions like RANK, DENSE_RANK, ROW_NUMBER to generate some ranking of your records.

Window functions are part of the ANSI SQL:2003 standard.
And I've at least encountered them on TeraData, Oracle and SQL-Server. 

select Id, RunId, Value, EnvironmentId
from (
  select i.*, r.EnvironmentId,
  dense_rank() over (partition by r.EnvironmentId order by r.RunId desc) as RN
  from issuetable i
  inner join runtable r on (i.RunId = r.RunId)
) Q
where RN = 1
order by Id;

The inner query would yield the following results :

Id  RunId   Value   EnvironmentId   RN
1   1       10      1               2
2   1       20      1               2
3   1       30      1               2
4   2       40      3               1
5   2       50      3               1
6   3       60      1               1
7   4       70      2               2
8   5       80      4               1
9   6       90      2               1

Upvotes: 3

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

Use a subquery to get the max runid for each environmentid from the runtable. Join the obtained result to the issuetable and select the required columns.

select i.id, i.runid, i.value, r.environmentid
from (select environmentid, max(runid) maxrunid
      from runtable 
      group by environmentid) r
join issuetable i on i.runid = r.maxrunid
order by i.runid, i.id

Upvotes: 3

Related Questions