none
Consulta sql

    Question

  • Hola.
    Alguien podria decirme como puedo hacer una sentencia sql que por ejemplo saque de estos registros:

    A  B  C  01-01-2008
    A  B  D  02-01-2008
    B  X  S  05-01-2008
    B  X  H  03-01-2008

    Si tengo estos registros con 4 campos y los 2 primeros son claves, quisiera una query que me saque por cada clave el ultimo de esa clave segun la fecha.

    Quisiera:
    A  B  D  02-01-2008
    B  X  S  05-01-2008

    Gracias...
     

    Sunday, March 16, 2008 4:03 PM

All replies

  • In English?  We may have spanish speaking members, but you may be waiting a while!

     

    I think you're asking how to fetch rows for each value in Column 1 pertaining to the MAX date in column 4 for that Column 1 value.  Is that correct?  If so, you can use this:

     

    Code Snippet

    SELECT mt.*

    FROM MyTable mt

    JOIN (SELECT Column1, MAX(Column4) AS MaxDate

             FROM MyTable

             GROUP BY Column1) a ON mt.Column1 = a.Column1 AND mt.Column4 = a.MaxDate

     

     

     

    Sunday, March 16, 2008 4:39 PM
    Moderator
  • Chema1,

     

    Cual versión de SQL Server estas usando?

     

    -- 2005

    ;with cte

    as

    (

    select

    c1, c2, c3, c4,

    row_number() over(partition by c1, c2 order by c4 DESC) as rn

    from

    tu_tabla

    )

    select c1, c2, c3, c4

    from cte

    where rn = 1;

     

    Tener un indice por (c1, c2, c4 DESC), pudiera ayudar a mejorar el rendimiento del query anterior.

     

    create index tu_tabla_c1_c2_c4_nu_nc

    on dbo.tu_tabla(c1, c2, c4 DESC) include (c3)

    go

     

    Si estas interesado, puedes postear tus preguntas en el grupo de noticias de SQL Server en Español.

     

    http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.es.sqlserver&cat=es_ES_199eb449-6090-436b-ab80-d4e1c16e27e6&lang=es&cr=ES

     

     

    AMB

     

     

    Sunday, March 16, 2008 5:11 PM
    Moderator
  • Original Post translated to English using Google Translate service.

     

    Hello.
    Someone could tell me how can I make a judgement that sql eg kick these records:

    A B C 01-01-2008
    A B D 02-01-2008
    B X S 05-01-2008
    B X M 03-01-2008

    If I have these records with 4 fields and the first 2 are key, which I would like to draw a query for each of the last key that key second date.

    Let me:
    A B D 02-01-2008
    B X S 05-01-2008

    Thanks ...
     
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------
    If you are using SQL Server 2005, you could use the following code.
     
    Si está utilizando SQL Server 2005, puede usar el siguiente código.
    [Traducido al español usando Google Translate]
     

    Code Snippet

    DECLARE @t TABLE (f1 CHAR(1), f2 CHAR(1), f3 CHAR(1), dt DATETIME)

    INSERT INTO @t (f1, f2, f3, dt) SELECT 'A','B','C','01-01-2008 '

    INSERT INTO @t (f1, f2, f3, dt) SELECT 'A','B','d','02-01-2008 '

    INSERT INTO @t (f1, f2, f3, dt) SELECT 'B','X','S','05-01-2008 '

    INSERT INTO @t (f1, f2, f3, dt) SELECT 'B','X','M','03-01-2008 '

     

    ;WITH cte AS (

    SELECT f1, f2, f3, dt,

    ROW_NUMBER() OVER(PARTITION BY f1, f2 ORDER BY dt DESC) AS seq

    FROM @t

    )

    SELECT f1, f2, f3, dt FROM cte

    WHERE seq = 1

    /*

    f1   f2   f3   dt

    ---- ---- ---- -----------------------

    A    B    d    2008-01-02 00:00:00.000

    B    X    S    2008-01-05 00:00:00.000

    */

     

     

    And here is the version for SQL Server 2000
     
    Y aquí está la versión de SQL Server 2000
    [Traducido al español usando Google Translate]
     

    Code Snippet

    DECLARE @t TABLE (f1 CHAR(1), f2 CHAR(1), f3 CHAR(1), dt DATETIME)

    INSERT INTO @t (f1, f2, f3, dt) SELECT 'A','B','C','01-01-2008 '

    INSERT INTO @t (f1, f2, f3, dt) SELECT 'A','B','d','02-01-2008 '

    INSERT INTO @t (f1, f2, f3, dt) SELECT 'B','X','S','05-01-2008 '

    INSERT INTO @t (f1, f2, f3, dt) SELECT 'B','X','M','03-01-2008 '

     

    SELECT mt.*

    FROM @t mt

    INNER JOIN (

    SELECT f1, f2, MAX(dt) AS MaxDate

    FROM @t

    GROUP BY f1, f2

    ) a ON mt.f1 = a.f1 AND mt.f2 = a.f2 AND mt.dt = a.MaxDate

    /*

    f1   f2   f3   dt

    ---- ---- ---- -----------------------

    A    B    d    2008-01-02 00:00:00.000

    B    X    S    2008-01-05 00:00:00.000

    */

     

     

    I dont speak spanish. Excuse me if the transalation is incorrect.

    regards

    Jacob

     

    Yo no hablan español. Perdóneme si la transalation es incorrecta.
    Cuanto
    Jacob
    [Traducido al español usando Google Translate]
    Sunday, March 16, 2008 5:12 PM
    Moderator
  • Ah, i see hunchback has answered it.

    Sunday, March 16, 2008 5:13 PM
    Moderator
  • Thanks, Hunchback.  Based upon your solution, I would have been at it for a while trying to figure it out!

     

    Sunday, March 16, 2008 5:46 PM
    Moderator