CODEWITHSUNDEEP
sqloracle-database
Weedoze
Weedoze

Reputation: 13943

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

Upvotes: 1

Views: 37

Answers (2)

Ming
Ming

Reputation: 211

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

Upvotes: 1

user1
user1

Reputation: 586

Something like this (?):

SELECT st.code_stas, st.nom_station, zzz.MAX_date_val_hor, vh.h_01, vh.h_02, vh.h_03
FROM (SELECT code_mesure, MAX(date_val_hor) AS MAX_date_val_hor FROM val_horaire GROUP BY code_mesure) ZZZ 
 INNER JOIN 
n_station st ON st.code_stas=zzz.code_mesure
INNER JOIN val_horaire vh
ON st.code_stas = vh.code_mesure
GROUP BY st.code_stas, st.nom_station, zzz.MAX_date_val_hor, vh.h_01, vh.h_02, vh.h_03;

Upvotes: 0

Related Questions