Weedoze Weedoze - 24 days ago 6
SQL Question

Query - Latest date from other table and the linked data

n_station

code_stas nom_station
1 StationA
2 StationB
3 StationC


val_horaire

code_mesure date_val_hor h_01 h_02 h_03
1 14/11/2016 23 29 32
1 15/11/2016 45 47 35
2 14/11/2016 12 15 13
2 15/11/2016 21 23 19
3 14/11/2016 74 75 79


I would like to get the latest (date) row of the table
val_horaire
and join it with table
n_station


Result

cod_stas nom_station date_val_hor h_01 h_02 h_03
1 StationA 15/11/2016 45 47 35
2 StationB 15/11/2016 21 23 19
3 StationC 14/11/2016 74 75 79


How can I achieve this ? The following query does not work

SELECT st.code_stas, st.nom_station, max(vh.date_val_hor), vh.h_01, vh.h_02, vh.h_03
FROM n_station st
INNER JOIN val_horaire vh
ON st.code_stas = vh.code_mesure
GROUP BY st.code_stas, st.nom_station, vh.h_01, vh.h_02, vh.h_03


This will show me multiple times a station

Answer

Solution No.1:

SELECT
    st.code_stas, 
    st.nom_station, 
    MAX(vh.date_val_hor) KEEP(DENSE_RANK FIRST ORDER BY st.nom_station DESC) AS date_val_hor,
    MAX(vh.h_01) KEEP(DENSE_RANK FIRST ORDER BY st.nom_station DESC) AS h_01, 
    MAX(vh.h_02) KEEP(DENSE_RANK FIRST ORDER BY st.nom_station DESC) AS h_02, 
    MAX(vh.h_03) KEEP(DENSE_RANK FIRST ORDER BY st.nom_station DESC) AS h_03
FROM
    n_station st,
    val_horaire vh
WHERE
    st.code_stas = vh.code_mesure
GROUP BY st.code_stas, st.nom_station

Solution No.2:

SELECT code_stas, nom_station, h_01, h_02, h_03 FROM (
    SELECT
        st.code_stas, 
        st.nom_station, 
        vh.date_val_hor, 
        vh.h_01, 
        vh.h_02, 
        vh.h_03,
        ROW_NUMBER() OVER(PARTITION BY st.code_stas, st.nom_station ORDER BY vh.date_val_hor DESC) AS DISTINCT_FLG 
    FROM
        n_station st,
        val_horaire vh
    WHERE
        st.code_stas = vh.code_mesure
)
WHERE DISTINCT_FLG = 1