CODEWITHSUNDEEP
sqlsql-server
Just_Stacking
Just_Stacking

Reputation: 405

Select row with latest date from a multi join

I am having trouble getting the 1 row with the latest date out of a multi-join statement.

3 tables involved:

(Unit)

SN                |            DateTime |
------------------|---------------------|
123               |2018-03-10 15:23:32  |
456               |2018-03-10 15:40:15  |
789               |2018-03-10 15:53:58  |

(History)

ID     |SN                |
-------|------------------|
84     |123               |
85     |456               |
86     |789               |

(Link)

SN                |      ID |
------------------|---------|
123               |84       |
456               |85       |
789               |86       |

(Station)

ID     |Type            |      datetime      |location                 |
-------|----------------|--------------------|-------------------------|
84     |ALPHA           |2017-08-21 16:54:23 |X                        |
84     |BRAVO           |2017-08-21 16:56:08 |X1                       |
277    |DELTA           |2017-08-21 17:46:11 |Y                        |

Goal

Get a joined table that will only show those rows with the most recent datetime per row and type selected.

I have managed to join up the tables, even sort the datetimes so that the most recent date is always the top 1, but I have no clue how then to select this top 1 I am interested in.

I have been at this now for several days no and cannot get only the one row that I need. I always end up with duplicates such as (Station) ID 84.

What I have tried before

SELECT rs.Unit, max(rf2.Type) as mydate,  rf2.Type, rf2.ID
FROM DB.dbo.Unit rs 
INNER JOIN DB.dbo.Link rf ON ( rs.ID = rf.ID  )  
INNER JOIN DB.dbo.History rf1 ON ( rf.SN = rf1.SN)  
INNER JOIN DB.dbo.Station rf2 ON ( rf1.ID = rf2.ID )         
where rf2.Type like 'AL%'
GROUP BY rs.SN, rf2.DateTime, rf2.Type, rf2.ID
order by rs.SN, rf2.DateTime desc;

I also tried a CTE, but same problem: I do not know how to only keep the most recent rows.

Result of above

SN                |mydate               |Type         |ID     |
------------------|---------------------|-------------|-------|
666               |2017-09-23 15:15:58  |Alpha        |189    |
777               |2017-09-01 16:13:16  |Alpha7       |138    |
*123*             |*2017-11-03 09:17:51*|*Alpha1*     |*84*   |
123               |2017-11-01 03:08:09  |Alpha2       |84     |
123               |2017-11-01 03:07:59  |Alpha3       |84     |

Basically what I would need is a table that only keeps unique rows such as the one marked with stars (123), most recent date, specific Type as in 'like 'AL%'.

After @Ferc's comment I tried something like this:

select ts.ID, ts.DateTime, ts.location 
from (select ID, DateTime, location 
row_number() over(partition by ID order by DateTime desc) as rn
from DB.dbo.Station where station like 'AL%') as ts
where rn = 1; 

I got "SQL Error [102] [S0001]: Incorrect syntax near '('.
Incorrect syntax near '('.
Incorrect syntax near '('." 
as an (complete rubbish) error message.

I then put a ',' after location and got a result.

I will try to incorporate this into my CTE.

Upvotes: 0

Views: 473

Answers (2)

D-Shih
D-Shih

Reputation: 46249

If I understand correct you might do like this.

You need to write a subquery with ROW_NUMBER with OVER(partition by rf2.ID,rs.SN order by rf2.[datetime] desc) you will get the group number on rf2.ID and rs.SN.

Then getting the RowNumber = 1 row,which mean newest date.

SELECT t.SN,t.[datetime],t.Type,t.ID
FROM(
  SELECT rs.SN,
         rf2.[datetime],
         rf2.[Type],
         rf2.ID,
         ROW_NUMBER() OVER(partition by rf2.ID,rs.SN order by rf2.[datetime] desc) rn
  FROM Unit rs 
  INNER JOIN Link rf ON ( rs.SN = rf.SN  )  
  INNER JOIN History rf1 ON ( rf.SN = rf1.SN)  
  INNER JOIN Station rf2 ON ( rf1.ID = rf2.ID )         
  WHERE rf2.Type like 'AL%'
) t 
where t.rn = 1

sqlfiddle:http://sqlfiddle.com/#!18/36061/2

Upvotes: 0

Wei Lin
Wei Lin

Reputation: 3821

select * from (
    select 
    row_number() over(partition by rf2.[datetime] order by rf2.[datetime] desc) as rn 
    ,rf2.*
    FROM Unit rs 
    INNER JOIN Link rf ON ( rs.SN = rf.SN  )  
    INNER JOIN History rf1 ON ( rf.SN = rf1.SN)  
    INNER JOIN Station rf2 ON ( rf1.ID = rf2.ID )  
    where rf2.Type like 'AL%'
) T
where rn = 1;

SQL Fiddle Example Link

Upvotes: 1

Related Questions