Select the record with the highest value within a JOIN

Navigation
1

I have a procedure with SELECT with several JOINS and, in one of them, I need to extract only one result (the most recent date) when several results can really be given. In principle, the dates will never be exact since it is stored in DD/MM/YYYY HH:mm:ss format and the date field is of type DATE .

As an example, we would have some questions like the following: 

SELECT a,b,c,d,e,f,g,h,i
FROM tabla3 t3
LEFT JOIN Tabla1 t1 ON t1.key = t3.key
LEFT JOIN Tabla2 t2 ON t2.key = t3.key
LEFT JOIN Vista_VW vw ON vw.key = t2.key 
     -- AND vw.FECHA // (en este JOIN quiero escoger sólo el registro de fecha mayor)

The value of the field FECHA does not interest me in the SELECT , I only need to use it as a filter of JOIN . The date is in the view VW

How to make the JOIN in the query that returns the record of FECHA most recent?

As a result I hope to get a pretty large data stack (for the other JOINS ) but this last JOIN I'm only interested in taking a record and I do not finish clear how to formulate the query SELECT .

    
asked by AXL 23.01.2017 в 16:37
source

2 answers

2

The window function ROW_NUMBER() adapts very well to what you ask. You can use the clause ORDER BY of the function to number the records in order of date. You can then filter by the records that were assigned the 1 range to limit the results to the records with the most recent date: 

SELECT a,b,c,d,e,f,g,h,i
FROM tabla3 t3
LEFT JOIN Tabla1 t1 ON t1.key = t3.key
LEFT JOIN Tabla2 t2 ON t2.key = t3.key
LEFT JOIN (
  SELECT vw.*, -- opcionalmente, puedes modificar esta parte si no necesitas todas las columnas de la vista
         row_number() over (
             partition by vw.key
             order by vw.fecha desc) as rn
    FROM Vista_VW vw
) vw ON vw.key = t2.key AND vw.rn = 1
    
answered by 01.02.2017 / 17:50
source
0

You can try running a query first to get the maximum date and then another for the comparison, you just need to adapt it to your needs. 

SELECT *
FROM tabla c2    
WHERE fechaemision = (
                      SELECT max(fechaemision)
                      FROM tabla
                      )
    
answered by 31.01.2017 в 15:12
Problem when using a service on Android Scala - Import error