Weedoze Weedoze - 9 days ago 6
SQL Question

ORACLE - Join definition table with data table

Given the two table shown below, I need to retrieve for each station, the latest values from the

VAL_MINUTES
table

N_STATION

| CODE_STAS | NOM_STATION | RIVIERE_BASSIN | LAMBERT_X | LAMBERT_Y |
|-----------|-------------|----------------|-----------|-----------|
| 5634 | StationA | RiverA | 50 | 60 |
| 2113 | StationB | RiverB | 55 | 95 |


VAL_MINUTES

| CODE_MESURE | DATE_VAL | M_05 | M_10 | M_15 | M_20 | M_25 | M_30 |
|-------------|------------|------|------|------|------|------|------|
| 563484 | 22/11/2016 | 0.02 | 0.35 | 0.48 | 0.2 | 0.65 | 0.45 |
| 563484 | 23/11/2016 | 1.2 | 0.9 | 1.15 | 0.84 | 0.89 | 1.02 |
| 563484 | 23/11/2016 | 1.2 | 0.9 | 1.15 | 0.84 | 0.89 | 1.02 |
| 211374 | 22/11/2016 | 0.03 | 0.1 | 0.24 | 0.21 | 0.18 | 0.19 |


RESULT EXPECTED

| CODE_STAS | DATE_VAL | M_05 | M_10 | M_15 | M_20 | M_25 | M_30 | NOM_STATION | RIVIERE_BASSIN | LAMBERT_X | LAMBERT_Y |
|-----------|------------|------|------|------|------|------|------|-------------|----------------|-----------|-----------|
| 5634 | 23/11/2016 | 1.2 | 0.9 | 1.15 | 0.84 | 0.89 | 1.02 | StationA | RiverA | 50 | 60 |
| 2113 | 22/11/2016 | 0.03 | 0.1 | 0.24 | 0.21 | 0.18 | 0.19 | StationB | RiverB | 55 | 95 |


NOTE


  • SUBSTR(code_mesure,1,4) = code_stas

  • It is possible to have multiple times the same date. We should use
    order by date_val desc
    and take the first one

  • As you can see with the
    code_mesure 563484
    there is the same line 2 times. It is a possible case. The latest (as seen above) will be used.






I tried the following query

SELECT *
FROM (
SELECT code_mesure,date_val,m_05,m_10,m_15,m_20,m_25,m_30,
rank () over (partition by code_mesure order by date_val desc) as rnk
FROM VAL_MINUTES) x
INNER JOIN N_STATION st
ON SUBSTR(x.code_mesure,1,4) = st.CODE_STAS
WHERE x.rnk = 1


But I get multiples times the same station because a station has a list of different code_mesure

|CODE_STAS |CODE_MESURE |
|----------|---------------------|
| 1021|102148, 102159,102132|


How can I get the expected result above ?

Answer

It is a bad idea to store a composed value (5634|84) in a single field. You should have two separate columns instead.

Anyway, you want to rank the data in table val_minutes. There can be ties and you want to pick a single record though. So use ROW_NUMBER (rather than RANK or DENSE_RANK) to give each record in a partition (i.e. within a code_stas) a number:

select 
  m.m_05, m.m_10, m.m_15, m.m_20, m.m_25, m.m_30, 
  s.nom_station, s.riviere_bassin, s.lambert_x, s.lambert_y
from n_station s
join
(
  select 
    substr(code_mesure,1,4) as code_stas,
    vm.*,
    row_number() over (partition by substr(code_mesure,1,4) order by date_val desc) as rnk
  from val_minutes vm
) m on m.code_stas = s.code_stas and m.rnk = 1;
Comments