How to implement multiple joins on different fields based on different functions in SQL?

Question

I have few tables as below. And, I need to fetch the records on the basis of each maximum level and latest level (ordered by date) for each ID and Type column. I'm using SQL Server to run the query. So far, I have tried the following SQL query:

select f.ID,x.MAX_LEVEL,f.TYPE, f.DATE
from (
 select ID
,TYPE
, MAX(LEVEL) as MAX_LEVEL
 from TABLEA
 GROUP BY ID, TYPE
 ) as x 
  ,
 (
  select ID
,TYPE
, MAX(DATE) as MAX_DATETIME
 from TABLEA
 GROUP BY ID, TYPE
 ) as y

 inner join TABLEA as f  
 on f.ID = x.ID and f.LEVEL = x.MAX_LEVEL

 inner join TABLEA as g 
 on f.ID = y.ID and g.DATE = y.MAX_DATETIME

 and f.DATE > DATEADD(day, -1, GETDATE())

TABLEA

ID  TYPE    LEVEL   DATE
1   ELECTRIC    2   01/06/2019
1   GAS         2   01/06/2019
2   ELECTRIC    2   01/06/2019
3   ELECTRIC    3   01/06/2019
3   ELECTRIC    3   01/06/2019
1   GAS         3   05/06/2019
1   GAS         5   13/06/2019
2   ELECTRIC    5   07/06/2019
3   GAS         5   08/06/2019
6   ELECTRIC    3   02/06/2019
2   ELECTRIC    3   04/06/2019
3   ELECTRIC    3   05/06/2019
2   GAS         10  06/06/2019
2   GAS         3   11/06/2019
3   ELECTRIC    3   11/06/2019
1   ELECTRIC    5   01/06/2019
1   GAS         3   02/06/2019
6   ELECTRIC    5   01/06/2019
1   ELECTRIC    5   10/06/2019

Expected Result:

ID  TYPE    MAX_LEVEL   LATEST_LEVEL
1   ELECTRIC    5       5
1   GAS         5       3
2   ELECTRIC    5       5
2   GAS         10      3
3   ELECTRIC    3       3
3   GAS         5       5
6   ELECTRIC    5       3

Any thoughts, how could I achieve this?

Please in code questions give a [mre]--cut & paste & runnable code; example input with desired & actual output (including verbatim error messages); tags & clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS/product & DDL which includes constraints & indexes & base table initialization. — philipxy, Jun 26 '19 at 19:18
Possible duplicate of [Select first row in each GROUP BY group?](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) — philipxy, Jun 26 '19 at 19:24

score 1 · Answer 1 · answered Jun 26 '19 at 10:21

if you are using sqlserver, you can try this.

SELECT ID, TYPE, MAX(T1.[LEVEL]) AS MAX_LEVEL, X.LEVEL AS LATEST_LEVEL
FROM TABLEA T1
    OUTER APPLY (SELECT TOP 1 [LEVEL] FROM TABLEA T2 WHERE T2.ID = T1.ID AND T2.TYPE = T1.TYPE ORDER BY T2.[DATE] DESC) X
GROUP BY ID, TYPE, X.[LEVEL] 
ORDER BY ID, TYPE

score 0 · Answer 2 · answered Jun 26 '19 at 10:28

Unfortunately, SQL Server doesn't have a "first" or "last" aggregation function. But it does have first_value() and last_value() as window functions. So, one method is:

select distinct t.id, t.type
       max(t.level) over (partition by id, type) as max_level,
       first_value(t.level) over (partition by id, type order by date desc) as latest_level
from t;

Another alternative is using window functions in a subquery:

select id, type, max(level) as max_level,
       max(case when seqnum = 1 then level end) as latest_level
from (select t.*,
             row_number() over (partition by id, type order by date desc) as seqnum
      from t
     ) t
group by id, type;

How to implement multiple joins on different fields based on different functions in SQL?

2 Answers2