none
Estrazione dati con clausola MAX o no RRS feed

  • Discussione generale

  • Salve ragazzi io ho questo problema (forse è talmente banale che non riesco a vedere la soluzione).
    tabella di 5 campi
    1° autoincrementate e indice primario
    2° intero
    3° intero
    4° datetime
    5° intero

    desidererei ( condizionale) estrarre tutti i record con clausola distinct dove ho la data più grande e il 5° campo = esempio 1

    DetPresId; PresId; UserId; TimeEvent; Effect
    24;1;12;2009-10-19 11:50:31.000;1
    25;1;13;2009-10-19 11:50:32.000;1
    27;1;13;2009-10-19 11:50:37.000;2
    28;1;12;2009-10-19 11:50:38.000;2
    31;1;11;2009-10-19 12:06:12.000;1

    esempio da questa tabella dovrei estrarre solamente l'ultimo record

     xche
    il record 27 ha la data più grande del record 25 ma effetto 2
    il record 28 idem con il 24
    l'unico valido sarebbe il 31

    non so se si può eseguire una singola query, ma per il momento non vedo una soluzione veloce.
    Se qualcuno la vede è ben accetto

    Ciao GianPiero
    • Tipo modificato Anca Popa venerdì 3 dicembre 2010 08:24 thread inattivo
    lunedì 19 ottobre 2009 13:06

Tutte le risposte

  • Salve ragazzi io ho questo problema (forse è talmente banale che non riesco a vedere la soluzione).
    tabella di 5 campi
    1° autoincrementate e indice primario
    2° intero
    3° intero
    4° datetime
    5° intero

    desidererei ( condizionale) estrarre tutti i record con clausola distinct dove ho la data più grande e il 5° campo = esempio 1
    Ciao GianPiero,

    Purtroppo il problema non mi è chiarissimo.
    La clausola DISTINCT non dovrebbe essere necessaria dato che restituendo una colonna IDENTITY le righe saranno sicuramente differenti tra loro (almeno per la colonna DetPresId).

    Io ti abbozzo un esempio basato su una CTE poi eventualmente ci lavoriamo sopra:

    USE tempdb;
    
    CREATE TABLE dbo.foo(
    DetPresId int NOT NULL PRIMARY KEY,
    PresId int NOT NULL,
    UserId int NOT NULL,
    TimeEvent datetime NOT NULL,
    Effect int NOT NULL
    );
    
    INSERT dbo.foo VALUES
          (24, 1, 12, '20091019 11:50:31', 1)
        , (25, 1, 13, '20091019 11:50:32', 1)
        , (27, 1, 13, '20091019 11:50:37', 2)
        , (28, 1, 12, '20091019 11:50:38', 2)
        , (31, 1, 11, '20091019 12:06:12', 1);
    
    WITH CTE_GetKey(TimeEvent) AS
    (
        SELECT MAX(TimeEvent)
        FROM dbo.foo
        WHERE Effect = 1
    )
    SELECT F.*
    FROM dbo.foo AS F
    JOIN CTE_GetKey AS C
    ON F.TimeEvent = C.TimeEvent
    WHERE F.Effect = 1;
    
    /* Output:
    
    DetPresId   PresId      UserId      TimeEvent               Effect
    ----------- ----------- ----------- ----------------------- -----------
    31          1           11          2009-10-19 12:06:12.000 1
    
    (1 row(s) affected)
    
    */
    
    DROP TABLE dbo.foo;

    La CTE restituisce la massima data per Effect = 1 e tale riga viene messa in JOIN con la tabella base, filtrando le righe con Effect = 1 (nel caso esistano più righe con Effect diversi a parità di data).

    Ciao!

    Lorenzo Benaglia
    Microsoft MVP - SQL Server
    http://blogs.dotnethell.it/lorenzo
    http://social.microsoft.com/Forums/it-IT/sqlserverit
    lunedì 19 ottobre 2009 19:00
    Moderatore
  • desidererei ( condizionale) estrarre tutti i record con clausola distinct dove ho la data più grande e il 5° campo = esempio 1

    Per scrivere una query è essenziale formularla, verbalmente, in maniera corretta e quanto dici sopra non mi pare che lo sia. Dall'esempio che fai mi sembra di capire che hai considerato il valore di PresId come termine di paragone per individuare un record in relazione ad un altro. Da quanto ho capito, correggimi se sbaglio, la query che ti serve è qualcosa di simile a

    SELECT A.DetPresId, A.PresId, A.UserId, A.TimeEvent, A.Effect
    FROM MyTable A LEFT JOIN MyTable B
     ON A.UserID = B.UserID
      AND B.Effect = 2
    WHERE A.Effect = 1
     AND A.TimeEvent > ISNULL(B.TimeEvent, 0)
     
    Bye

    Luca Bianchi
    Microsoft MVP - SQL Server
    lunedì 19 ottobre 2009 19:15
  • Grazie Lorenzo della risposta.
    La cosa sembra interessante, comincio a pensare che sia possibile.

    Forse non mi sono spiegato bene, cercherò di essere più chiaro possibile.

    Premetto che ho gia un trigger sull'insert della tabella che mi elimina tutti i record con

    stesso PresId, stesso UserId e TimeEvent  < di quello inserito (a prescindere da Effect)
    questo mi toglie un sacco di problemi e l'unica situazione anomala che può esistere è
    simile quella dell'esempio da me postato.

    24;1;13;2009-10-19 11:50:37.000;1
    25;1;12;2009-10-19 11:50:38.000;1
    26;1;12;2009-10-19 11:50:31.000;2
    27;1;13;2009-10-19 11:50:32.000;2
    31;1;11;2009-10-19 12:06:12.000;1

    in questo caso dovrei estrarre i record 24,25,31


    I campi che mi interessa estrarre sono
    PresId,UserId,TimeEvent

    ogni record estratto deve soddisfare le seguenti condizioni

    Effect = 1
    che con stesso PresId e UserId sia quello con TimeEvent più grande e che
    con gli stessi PresId e UserId non sia presente un record con TimeEvent maggiore ma effect = 2

    Spero di essermi spiegato
    Comunque con il trigger in funzione non dovrebbe mai susssitere una condizione nella quale ho più di 2 record
    con stesso PresId e UserId



    lunedì 19 ottobre 2009 20:41
  • Grazie Luca e scusa se non mi sono spiegato correttamente.
    Quello che mi serve l'ho scritto al tuo collega Lorenzo e siccome potrebbe essere
    una buona starda un join su se stessa spero che tu mi possa aiutare

    Bye
     

    lunedì 19 ottobre 2009 20:53
  • Premetto che ho gia un trigger sull'insert della tabella che mi elimina tutti i record con

    stesso PresId, stesso UserId e TimeEvent  < di quello inserito (a prescindere da Effect)
    questo mi toglie un sacco di problemi e l'unica situazione anomala che può esistere è
    simile quella dell'esempio da me postato.

    24;1;13;2009-10-19 11:50:37.000;1
    25;1;12;2009-10-19 11:50:38.000;1
    26;1;12;2009-10-19 11:50:31.000;2
    27;1;13;2009-10-19 11:50:32.000;2
    31;1;11;2009-10-19 12:06:12.000;1

    in questo caso dovrei estrarre i record 24,25,31


    I campi che mi interessa estrarre sono
    PresId,UserId,TimeEvent

    ogni record estratto deve soddisfare le seguenti condizioni

    Effect = 1
    che con stesso PresId e UserId sia quello con TimeEvent più grande e che
    con gli stessi PresId e UserId non sia presente un record con TimeEvent maggiore ma effect = 2

    Spero di essermi spiegato


    Mica troppo :-(
    Che significa "con gli stessi PresId e UserId non sia presente un record con TimeEvent maggiore ma effect = 2"?!
    Perché non posti un esempio completo come ho fatto io con la struttura della tabella, alcune righe di prova ed il result set che ti aspetti?

    Ciao!
    Lorenzo Benaglia
    Microsoft MVP - SQL Server
    http://blogs.dotnethell.it/lorenzo
    http://social.microsoft.com/Forums/it-IT/sqlserverit
    lunedì 19 ottobre 2009 20:54
    Moderatore
  • Non pensavo fosse cosi difficile spiegarsi bene.
    Comunque l'esempio che hai postato calza a pennello soltanto che se cambio il valore di Effect il risultato non è più corretto.

    Non ti scrivo tutto il tuo esempio, ma la tabella va bene cosi come l'hai  creata
    se esguiamo questo insert

     

     

    INSERT dbo.foo VALUES
          (24, 1, 12, '20091019 11:50:31', 2)
        , (25, 1, 13, '20091019 11:50:32', 2)
        , (27, 1, 13, '20091019 11:50:37', 1)
        , (28, 1, 12, '20091019 11:50:38', 1)
        , (31, 1, 11, '20091019 12:06:12', 1);

    otterremmo lo stesso risultato invece in questo caso
    i record estratti dovrebbero essere gli ultimi 3

    Ciao

    lunedì 19 ottobre 2009 21:28
  • Non ti scrivo tutto il tuo esempio, ma la tabella va bene cosi come l'hai  creata
    se esguiamo questo insert

    INSERT dbo.foo VALUES
          (24, 1, 12, '20091019 11:50:31', 2)
        , (25, 1, 13, '20091019 11:50:32', 2)
        , (27, 1, 13, '20091019 11:50:37', 1)
        , (28, 1, 12, '20091019 11:50:38', 1)
        , (31, 1, 11, '20091019 12:06:12', 1);

    otterremmo lo stesso risultato invece in questo caso
    i record estratti dovrebbero essere gli ultimi 3


    Non ho ancora capito il significato della frase "con gli stessi PresId e UserId non sia presente un record con TimeEvent maggiore ma effect = 2" dato che non me l'hai spiegato, comunque questo esempio restituisce il risultato richiesto (anche se non ho idea se sia corretto o meno dato che non ho capito nel dettaglio la logica di estrazione che hai in mente):

    USE tempdb;
    
    CREATE TABLE dbo.foo(
    DetPresId int NOT NULL PRIMARY KEY,
    PresId int NOT NULL,
    UserId int NOT NULL,
    TimeEvent datetime NOT NULL,
    Effect int NOT NULL
    );
    
    INSERT dbo.foo VALUES
          (24, 1, 12, '20091019 11:50:31', 2)
        , (25, 1, 13, '20091019 11:50:32', 2)
        , (27, 1, 13, '20091019 11:50:37', 1)
        , (28, 1, 12, '20091019 11:50:38', 1)
        , (31, 1, 11, '20091019 12:06:12', 1);
    
    SELECT PresId, UserId, MAX(TimeEvent) AS TimeEvent
    FROM dbo.foo
    WHERE Effect = 1
    GROUP BY PresId, UserId;
    
    /* Output:
    
    PresId      UserId      TimeEvent
    ----------- ----------- -----------------------
    1           13          2009-10-19 11:50:37.000
    1           12          2009-10-19 11:50:38.000
    1           11          2009-10-19 12:06:12.000
    
    (3 row(s) affected)
    
    */
    
    DROP TABLE dbo.foo;

    Ciao!
    Lorenzo Benaglia
    Microsoft MVP - SQL Server
    http://blogs.dotnethell.it/lorenzo
    http://social.microsoft.com/Forums/it-IT/sqlserverit
    lunedì 19 ottobre 2009 21:37
    Moderatore
  • significa che se avessi un record ...
     se fosse cosi

    INSERT dbo.foo VALUES
          (24, 1, 12, '20091019 11:50:31', 1) *
        , (25, 1, 13, '20091019 11:50:32', 2)
        , (27, 1, 13, '20091019 11:50:37', 1)
        , (28, 1, 12, '20091019 11:50:38', 2) **
        , (31, 1, 11, '20091019 12:06:12', 1);

    questo record
    (24, 1, 12, '20091019 11:50:31', 1) *

    non andrebbe estratto xche rispetto al suo omonimo
    (28, 1, 12, '20091019 11:50:38', 2) **

    ha la data minore anche se di effect = 1

    credo che adesso abbiamo elencato tutti i possibili casi

    Interessante CTE_GetKey non l'ho mai usata devo studiarmela un pò
     
    Ciao


        
    lunedì 19 ottobre 2009 22:04
  • Mi spiace se non sono riuscito a spiegarmi correttamente
    il fatto è che è io dovrei estrarre da questa tabella
    i record che hanno effetto 1,
    ma a parità di parametri (PresId e UserId) solo quello con la data maggiore,
    e solamente sempre a parità di parametri se non esiste un record con la data maggiore ma con effetto 2

    Non avendo trovato una select che soddisfa le mie esigenze(probabilmente perchè non ho spiegato correttamente il problema) l'ho risolta  inserendo un tringger sull'insert

    in questo modo effettuo un controllo su ogni riga inserita ed elimino tutto cio che non è necessario resti nella suddetta tabella
    ovverosia tutti i record che hanno la data inferiore all'ultimo inserito,
    in questo modo al massimo posso ritrovarmi solamente 2 record che hanno gli stessi parametri ma data diversa,
    e in questo caso la seguente è più che sufficente
     
    La tabella in questione si chiama DetPres

    SELECT PresId, UserId, MAX(TimeEvent) FROM dbo.DetPres
    WHERE Effect = 1
    GROUP BY PresId, UserId;

    ovviamente a questa select vanno aggiunti i join per ricavare i campi descrittivi riferiti agli indici PresId e UserId, ma non è un problema

    Se a qualcuno interessa il trigger è questo

    ALTER

     

    TRIGGER [dbo].[DetPres_Trigger_On_Insert]
    ON [dbo].[DetPres]
    AFTER INSERT
    AS
    BEGIN
    SET NOCOUNT ON;
    DECLARE
    @PRESID BIGINT,
    @USERID BIGINT,
    @TIMEEVENT DATETIME,
    @EFFECT TINYINT,
    @LASTOUTTIMEEVENT DATETIME
    carico i parametri dell'ultimo inserimento
    SELECT
    @PRESID = PRESID, @USERID = USERID, @TIMEEVENT = TIMEEVENT, @EFFECT = EFFECT FROM INSERTED
    viene eseguito solo se l'ultimo inserimento ha effect 1
    IF
    (@EFFECT < 2)
    BEGIN
    SELECT @LASTOUTTIMEEVENT = TIMEEVENT FROM DETPRES
    WHERE
    PRESID = @PRESID
    AND
    USERID = @USERID
    AND
    EFFECT = 2
    IF (NOT(@LASTOUTTIMEEVENT IS NULL))
    DELETE FROM DETPRES
    WHERE
    PRESID = @PRESID
    AND
    USERID = @USERID
    AND
    TIMEEVENT < @LASTOUTTIMEEVENT
    END

     

    questo viene eseguito sempre per cui nella tabella ho sempre un solo record al massimo 2 xche potrebbe esserne inserito 1
    con la data <

    DELETE
    FROM DETPRES
    WHERE
    PRESID = @PRESID
    AND
    USERID = @USERID
    AND
    TIMEEVENT < @TIMEEVENT

    END

    ho fatto delle prove incrociate e sembra funzionare correttamente.

    mi spiace per l'indent ma l'incolla qui non funziona troppo bene

    Non so se considerare chiuso o no questo post

    Ciao e grazie
    GianPiero
    PS
    Il riferimente alla CTE mi ha fatto venire un dubbio su una mega select che ho fatto e che forse è possibile migliorarne la velocità di esecuzione
    ma suppongo di dover aprire on nuovo Thread

    mercoledì 21 ottobre 2009 07:22