0

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.

Just_Stacking
  • 395
  • 3
  • 13
  • 1
    Instead of a `group by`, you can use [ROW_NUMBER](https://learn.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-2017). See [here](https://stackoverflow.com/a/7118233/1733214) – ferc Apr 26 '18 at 01:42
  • 1
    Could you show us your expect result from your sample data?Becasue **Result of above** can't match your sample data.What's the diffrent between History and Link table? – D-Shih Apr 26 '18 at 02:07
  • @Ferc: Thanks for the link. I had seen something like this before, but never got it to work. Updated the question with what I tried. I only get an error near: '('. Whatever that is supposed to mean.... – Just_Stacking Apr 26 '18 at 02:11
  • 1
    @Just_Stacking Ok... well, perhaps you had a syntax error somewhere. IT WeiHan seems to have the syntax right, but I think for your case, you'd need to partition by `[Type]` or `[SN]`, not the date field. – ferc Apr 26 '18 at 02:28
  • @Ferc: Yes, correct. I managed to figure this out. Thanks! IT WeiHan's answer seems to work perfectly when I change the dummy names with my real table names. Also getting all other fields from the other tables seems to work fine, so that I end up with one big, happy table. I think I will keep this as the answer ,since it was the quickest to arrive. – Just_Stacking Apr 26 '18 at 03:07
  • @D-Shih: Thanks, for pointing that out. The "Hierarchy" of tables was same as the order they came in, so simply joining them works to get to the required table. – Just_Stacking Apr 26 '18 at 03:11

2 Answers2

1
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

Wei Lin
  • 3,591
  • 2
  • 20
  • 52
0

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

D-Shih
  • 44,943
  • 6
  • 31
  • 51