I have this information,

header table
documento    fecha_entrada
100000        2005-11-25
100001        2005-11-25
100002        2005-11-26

detail table
documento    linea   numero_producto    fecha
100000        1        X                            2005-11-25 10:00
100000        2        Y                            2005-11-25 10:01
100001        1        M                           2005-11-25 11:02
100001        2        S                            2005-11-25 11:03
100001        3        R                            2005-11-25 11:04
100002        1        R                            2005-11-25 11:04

I want to make a SELECT query to obtein this
docuemnto    linea    numero_pro            fecha
fecha_entrada
100000        2        Y                            2005-11-25 10:01
2005-11-25
100001        3        R                            2005-11-25 11:04
2005-11-25
100002        1        R                            2005-11-25 11:04
2005-11-26


I only want the header and ONE row of the detail... the row has to be the
most recent captured..

On Fri, 25 Nov 2005 16:56:18 -0600, Myriam Cerda wrote:

Hi Myriam,

Try this:

SELECT     h.documento, d.linea, d.numero_producto,
           d.fecha, h.fecha_entrada
FROM       header AS h
INNER JOIN detail AS d
      ON   d.documento = h.documento
WHERE      d.fecha = (SELECT MAX(d2.fecha)
                      FROM   detail AS d2
                      WHERE  d2.documento = h.documento)

(untested - see www.aspfaq.com/5006 if you prefer a tested reply)

Best, Hugo

Hugo, Thanks  for your prompt response, It's works...



Show quoteHide quote

Trasform columns to rows
Still struggling w/ LAST transaction date!!!
DISTINCT MonthName for a lot of dates....
selecting from a 6 month date range
Sum() And Nulls