none
get the latest record in a group RRS feed

  • Question

  • I have a table that looks like this:

    UniqueID    LoginID    Desc    Status    LogDate
    1        ABC1    Descr1    GOOD    1/1/2010
    2        ABC1    Descr2    GOOD    1/1/2010
    3        ABC1    Descr1    BAD    2/2/2010
    4        ABC1    Descr1    OK    3/3/2010
    5        DEF1    Descr1    BAD    4/4/2010
    6        DEF1    Descr2    BAD    5/5/2010
    7        DEF1     Descr2    GOOD    5/5/2010

    What I am trying to do is I want to get the latest (in terms of LogDate ) record based on LoginID and Desc so that the return result should look like this:

    LoginID    Desc    Status    LogDate
    ABC1    Descr2    GOOD    1/1/2010
    ABC1    Descr1    OK    3/3/2010
    DEF1    Descr1    BAD    4/4/2010
    DEF1     Descr2    GOOD    5/5/2010

    How can I reach this result?

    Any help is appreaciated.


    PS:On my best try I get 5 records (instead of 4) because the record on UniqueID=6 and 7 shows twice (because they share the same LoginID , Desc , and "LogDate ")
    Thursday, April 29, 2010 8:24 PM

Answers

  • Take a look, please

    Including an Aggregated Column's Related Values
    Including an Aggregated Column's Related Values - Part 2
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, April 29, 2010 8:31 PM
    Moderator
  • I finally read your "P.S." at the end of the message.

    If something has the same LoginID, Desc, and LogDate, then do you want to choose the row with the highest UniqueID?

    If so, then I'll amend my original code:

    ;WITH cte AS
    (
      SELECT LoginID, Desc, Status, LogDate, RNum=ROW_NUMBER() OVER (PARTITION BY LoginID, Desc ORDER BY LogDate DESC, UniqueID DESC)
      FROM MyTable
    )
    SELECT LoginID, Desc, Status, LogDate
    FROM cte
    WHERE RNum=1


    --Brad (My Blog)
    Thursday, April 29, 2010 8:38 PM
    Moderator
  • Thanks Brad, yes it should get the latest UniqueId in that case. I am trying to run this code but the environment I am using doesnt seem like accepting "WITH cte AS" (i tried with and without the semicol) it produces a syntax error.

    I am still trying to find a way around it.

     

    Thanks


    What version of sql are you using?  CTE are only available in SQL 2005+.

    If you are using SQL 2000, try this code.

    DECLARE @t TABLE(
    UniqueID INT,
    LoginID VARCHAR(10),
    [DESC] VARCHAR(10),
    [Status] VARCHAR(10),
    LogDate DATETIME
    );
    
    INSERT INTO @t VALUES (1,'ABC1','Descr1','GOOD','1/1/2010');
    INSERT INTO @t VALUES (2,'ABC1','Descr2','GOOD','1/1/2010');
    INSERT INTO @t VALUES (3,'ABC1','Descr1','BAD','2/2/2010');
    INSERT INTO @t VALUES (4,'ABC1','Descr1','OK','3/3/2010');
    INSERT INTO @t VALUES (5,'DEF1','Descr1','BAD','4/4/2010');
    INSERT INTO @t VALUES (6,'DEF1','Descr2','BAD','5/5/2010');
    INSERT INTO @t VALUES (7,'DEF1','Descr2','GOOD','5/5/2010');
    
    SELECT t.LoginId,t.[Desc],MAX(t.[Status]) AS [Status],t.LogDate
    FROM @t t
    INNER JOIN(
    	SELECT MAX(LogDate) AS LogDate,LoginId, [DESC]
    	FROM @t t
    	GROUP BY LoginId, [DESC]
    ) AS t2
    ON t.[LogDate] = t2.[LogDate] 
    	AND t.[LoginID] = t2.LoginId
    	AND [t].[DESC] = [t2].[DESC]
    GROUP BY 
    	t.LoginId,
    	t.[Desc],
    	t.[LogDate]
    

    http://jahaines.blogspot.com/
    Thursday, April 29, 2010 9:25 PM
    Moderator
  • If column [UniqueID] can be used to identify rows chronologically, then you can you use:

    SELECT
      T2.UniqueID,
      T2.LoginID,
      T2.[DESC],
      T2.Status,
      T2.LogDate
    FROM
    	(
      SELECT
        LoginID,
        [DESC],
        MAX(UniqueID) AS max_UniqueID
      FROM
    	  @t
      GROUP BY
        LoginID,
        [DESC]
      ) AS T1
      INNER JOIN
      @t AS T2
      ON T1.LoginID = T2.LoginID
      AND T1.[DESC] = T2.[DESC]
      AND T1.max_UniqueID = T2.UniqueID
    ORDER BY
     T2.UniqueID; 
    GO

    Thanks to Adam for providing table schema and sample data.

    AMB

    Thursday, April 29, 2010 11:54 PM
    Moderator
  • Yes.  You are correct.  I didn't catch that in the longer threads...  I changed the query though to exclude the UniqueID altogther and it still performs very nicely with only one table scan:

    create table #temp (UniqueID int, LoginID char(4), [Desc] char(6), Status varchar(4), LogDate datetime)
    insert into #temp select 1, 'ABC1', 'Descr1', 'GOOD', '1/1/2010'
    insert into #temp select 1, 'ABC1', 'Descr2', 'GOOD', '1/1/2010'
    insert into #temp select 1, 'ABC1', 'Descr1', 'BAD', '2/2/2010'
    insert into #temp select 1, 'ABC1', 'Descr1', 'OK', '3/3/2010'
    insert into #temp select 1, 'DEF1', 'Descr1', 'BAD', '4/4/2010'
    insert into #temp select 1, 'DEF1', 'Descr2', 'BAD', '5/5/2010'
    insert into #temp select 1, 'DEF1', 'Descr2', 'GOOD', '5/5/2010'
    insert into #temp select 1, 'DEF1', 'Descr2', 'GOOD', '5/5/2010'
    
    select * from #temp
    
    select 
     y.LoginID, y.[Desc], 
     case y.TempID % 1 * 10 
      when 3 then 'GOOD' 
      when 2 then 'OK' 
      else 'BAD' 
     end as Status, 
     dateadd(dd, cast(y.TempID as int), '1/1/1990') as LogDate 
    from (
     select x.LoginID, x.[Desc], max(x.TempID) as TempID 
     from (
      select 
       LoginID, [Desc], 
       cast(datediff(dd, '1/1/1990', LogDate) as decimal(10,1)) + 
        case Status 
         when 'Good' then .3 
         when 'Ok' then .2 
         else .1 
        end as TempID
      from #temp 
      group by 
       LoginID, [Desc], [Status], 
       cast(datediff(dd, '1/1/1990', LogDate) as decimal(10,1)) + 
        case Status 
         when 'Good' then .3 
         when 'Ok' then .2 
         else .1 
        end
     ) as x
     group by x.LoginID, x.[Desc]
    ) as y
    order by 4, 1, 2
    go
    drop table #temp
    Friday, April 30, 2010 9:49 PM

All replies

  • If you have SQL2005, you can do this:

    ;WITH cte AS
    (
      SELECT LoginID, Desc, Status, LogDate, RNum=ROW_NUMBER() OVER (PARTITION BY LoginID, Desc ORDER BY LogDate DESC)
      FROM MyTable
    )
    SELECT LoginID, Desc, Status, LogDate
    FROM cte
    WHERE RNum=1

     


    --Brad (My Blog)
    Thursday, April 29, 2010 8:27 PM
    Moderator
  • Take a look, please

    Including an Aggregated Column's Related Values
    Including an Aggregated Column's Related Values - Part 2
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, April 29, 2010 8:31 PM
    Moderator
  • I finally read your "P.S." at the end of the message.

    If something has the same LoginID, Desc, and LogDate, then do you want to choose the row with the highest UniqueID?

    If so, then I'll amend my original code:

    ;WITH cte AS
    (
      SELECT LoginID, Desc, Status, LogDate, RNum=ROW_NUMBER() OVER (PARTITION BY LoginID, Desc ORDER BY LogDate DESC, UniqueID DESC)
      FROM MyTable
    )
    SELECT LoginID, Desc, Status, LogDate
    FROM cte
    WHERE RNum=1


    --Brad (My Blog)
    Thursday, April 29, 2010 8:38 PM
    Moderator
  • Brad, I was thinking the same but though a case statement going from good via okay through to bad instead of UniqueID as your post above might be the preferred order?

     

    RNum=ROW_NUMBER() OVER (PARTITION BY LoginID, [Desc] ORDER BY LogDate DESC,
    CASE [Status] WHEN 'GOOD' THEN 2 WHEN 'OK' THEN 1 ELSE 0 END DESC)
    

     

    Dave

    Thursday, April 29, 2010 8:51 PM
  • Thanks Brad, yes it should get the latest UniqueId in that case. I am trying to run this code but the environment I am using doesnt seem like accepting "WITH cte AS" (i tried with and without the semicol) it produces a syntax error.

    I am still trying to find a way around it.

     

    Thanks

    Thursday, April 29, 2010 9:02 PM
  • Still no luck,

    I was wondering if it can be done without using the WITH clause?

     

    Thursday, April 29, 2010 9:16 PM
  • Still no luck,

    I was wondering if it can be done without using the WITH clause?

     


    Yes, it can...

    SELECT LoginID, Desc, Status, LogDate
    FROM
    (
      SELECT LoginID, Desc, Status, LogDate,
                     RNum=ROW_NUMBER() OVER (PARTITION BY LoginID, Desc ORDER BY LogDate DESC, UniqueID DESC)
      FROM MyTable
    ) X
    WHERE RNum=1

    However, if you had trouble with the WITH clause, then you might have trouble with ROW_NUMBER() as well.

    Are you on SQL2005 (or higher)?  What is the compatibility level of the database you're working on?

     


    --Brad (My Blog)
    Thursday, April 29, 2010 9:24 PM
    Moderator
  • Thanks Brad, yes it should get the latest UniqueId in that case. I am trying to run this code but the environment I am using doesnt seem like accepting "WITH cte AS" (i tried with and without the semicol) it produces a syntax error.

    I am still trying to find a way around it.

     

    Thanks


    What version of sql are you using?  CTE are only available in SQL 2005+.

    If you are using SQL 2000, try this code.

    DECLARE @t TABLE(
    UniqueID INT,
    LoginID VARCHAR(10),
    [DESC] VARCHAR(10),
    [Status] VARCHAR(10),
    LogDate DATETIME
    );
    
    INSERT INTO @t VALUES (1,'ABC1','Descr1','GOOD','1/1/2010');
    INSERT INTO @t VALUES (2,'ABC1','Descr2','GOOD','1/1/2010');
    INSERT INTO @t VALUES (3,'ABC1','Descr1','BAD','2/2/2010');
    INSERT INTO @t VALUES (4,'ABC1','Descr1','OK','3/3/2010');
    INSERT INTO @t VALUES (5,'DEF1','Descr1','BAD','4/4/2010');
    INSERT INTO @t VALUES (6,'DEF1','Descr2','BAD','5/5/2010');
    INSERT INTO @t VALUES (7,'DEF1','Descr2','GOOD','5/5/2010');
    
    SELECT t.LoginId,t.[Desc],MAX(t.[Status]) AS [Status],t.LogDate
    FROM @t t
    INNER JOIN(
    	SELECT MAX(LogDate) AS LogDate,LoginId, [DESC]
    	FROM @t t
    	GROUP BY LoginId, [DESC]
    ) AS t2
    ON t.[LogDate] = t2.[LogDate] 
    	AND t.[LoginID] = t2.LoginId
    	AND [t].[DESC] = [t2].[DESC]
    GROUP BY 
    	t.LoginId,
    	t.[Desc],
    	t.[LogDate]
    

    http://jahaines.blogspot.com/
    Thursday, April 29, 2010 9:25 PM
    Moderator
  • Brad,

    Because the db environment we are using is newly introduced third party app i am not sure with what kind of sql it is compatable with. I tried your new query and you were right, it threw an error by ROW_NUMBER()

     

     

    Adam,

    I have two questions for you,

    1- Why are you doing MAX(t.Status) ? it is string, wouldnt MAX cause a problem?

    2- Does it handle the situation I mentioned under "PS" of my first post?

     

    Thank you both in advance

    Thursday, April 29, 2010 9:50 PM
  • Brad,

    Because the db environment we are using is newly introduced third party app i am not sure with what kind of sql it is compatable with. I tried your new query and you were right, it threw an error by ROW_NUMBER()

     

     

    Adam,

    I have two questions for you,

    1- Why are you doing MAX(t.Status) ? it is string, wouldnt MAX cause a problem?

    2- Does it handle the situation I mentioned under "PS" of my first post?

     

    Thank you both in advance


    The second MAX is to handle when you have a situation where everything is the same as in your PS.  Essentially i am taking the max status code, for a given grouping.
    http://jahaines.blogspot.com/
    Thursday, April 29, 2010 10:35 PM
    Moderator
  • If column [UniqueID] can be used to identify rows chronologically, then you can you use:

    SELECT
      T2.UniqueID,
      T2.LoginID,
      T2.[DESC],
      T2.Status,
      T2.LogDate
    FROM
    	(
      SELECT
        LoginID,
        [DESC],
        MAX(UniqueID) AS max_UniqueID
      FROM
    	  @t
      GROUP BY
        LoginID,
        [DESC]
      ) AS T1
      INNER JOIN
      @t AS T2
      ON T1.LoginID = T2.LoginID
      AND T1.[DESC] = T2.[DESC]
      AND T1.max_UniqueID = T2.UniqueID
    ORDER BY
     T2.UniqueID; 
    GO

    Thanks to Adam for providing table schema and sample data.

    AMB

    Thursday, April 29, 2010 11:54 PM
    Moderator
  • Alejandro,

    What if UniqueID doesn't come in the chronological order? In this case your solution would not work.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, April 30, 2010 12:01 AM
    Moderator
  • Naomi,

    Didn't I start saying "IF column [UniqueID] can be used to ..."?

    I know my english is not as good as I would like it to be, but I think in this case I was clear enough.

    It is like saying that Adam's suggestion will not work if some rows with same [LoginID] and [DESC] share same [LogDate], and we want the greatest [UniqueID] instead the greatest [Status].

    The OP have to be more clear regards the criteria we should use to break the tie. 

    BTW, here is another way to solve this problem. Again, using [UniqueID] to break the tie, but you can [Status] if you like to.

    SELECT
     *
    FROM
     @t AS T1
    WHERE
     NOT EXISTS (
     SELECT *
     FROM @t AS T2
     WHERE
      T2.LoginID = T1.LoginID
      AND T2.[DESC] = T1.[DESC]
      AND (
      T1.LogDate < T2.LogDate
      OR (T1.LogDate = T2.LogDate AND T1.UniqueID < T2.UniqueID)
      )
     );
    GO

     

    AMB

     

    Friday, April 30, 2010 1:15 AM
    Moderator
  • Ok, I ran lots of queries. The packed value solution was a performance winner, then Adam's query (which produced incorrect results on my "tricky" data), then Alejandro's solution, then my subquery solution.

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

    Here is the query I tried (I ran it many times):

    -- Test query
    /*
    create TABLE T(
    UniqueID INT identity primary key,
    LoginID VARCHAR(10),
    [DESC] VARCHAR(10),
    [Status] VARCHAR(10),
    LogDate DATETIME
    );*/
    
    set nocount on
    
    declare @i int = 0
    while @i < 10000
    begin
    INSERT INTO T VALUES ('ABC1','Descr1','GOOD','1/1/2010'),
     ('ABC1','Descr2','GOOD','1/1/2010'),
     ('ABC1','Descr1','BAD','2/2/2010'),
     ('ABC1','Descr1','OK','3/3/2010'),
    ('DEF1','Descr1','NOT BAD','4/4/2010'),
    ('DEF1','Descr1','BAD','4/4/2010'),
    ('DEF1','Descr2','BAD','5/5/2010'),
    ('DEF1','Descr2','GOOD','5/5/2010'),
    ('ABC1','Descr1','BAD','2/2/2010'),
    ('ABC1','Descr1','OK','3/2/2010')
     set @i = @i+ 1
    end
    go
    
    declare @cnt int
    select @cnt =COUNT(*) from T
    print 'Table T has ' + cast(@cnt as varchar(10)) + ' records'
    
    print 'Adam''s query'
    set statistics io on
    set statistics time on
    SELECT t.LoginId,t.[Desc],MAX(t.[Status]) AS [Status],t.LogDate
    FROM t
    INNER JOIN(
    	SELECT MAX(LogDate) AS LogDate,LoginId, [DESC]
    	FROM t
    	GROUP BY LoginId, [DESC]
    ) AS t2
    ON t.[LogDate] = t2.[LogDate] 
    	AND t.[LoginID] = t2.LoginId
    	AND [t].[DESC] = [t2].[DESC]
    GROUP BY 
    	t.LoginId,
    	t.[Desc],
    	t.[LogDate]
    order by LoginID, [Desc]	
    set statistics time off	
    set statistics io off	
    print 'Naomi''s query'
    set statistics io on
    set statistics time on
    SELECT UniqueID, LoginID, [Desc], [Status], LogDate
    FROM T where UniqueID = 
    (select top 1 UniqueID from T T1 where T1.LoginID = T.LoginID and 
    T1.[Desc] = T.[Desc] order by LogDate DESC, UniqueID DESC) order by LoginID, [Desc], UniqueID
    set statistics time off
    set statistics io off
    
    
    print 'Alejandro''s query'
    set statistics io on
    set statistics time on
    SELECT UniqueID, LoginID, [Desc], [Status], LogDate
    FROM
     T AS T1
    WHERE
     NOT EXISTS (
     SELECT *
     FROM T AS T2
     WHERE
     T2.LoginID = T1.LoginID
     AND T2.[DESC] = T1.[DESC]
     AND (
     T1.LogDate < T2.LogDate
     OR (T1.LogDate = T2.LogDate AND T1.UniqueID < T2.UniqueID)
     )
     ) Order BY LoginID, [Desc], UniqueID ;
     set statistics time off
     set statistics io off
     print 'Packed value solution'
     
     set statistics io on
     set statistics time on
     
    SELECT cast(substring(Compound,11,10) as int) as UniqueID, LoginID, [Desc], cast(right(Compound,10) as varchar(10)) as [Status], LogDate
    FROM (select max(LogDate) as LogDate, 
    max(convert(binary(10),LogDate,112)+ CAST(UniqueID as binary(10)) + cast([Status] as binary(10))) as Compound,
    [Desc], [LoginID] from T group by [LoginID],[Desc]) X order by LoginID, [Desc], UniqueID

    And these are the results:

    SQL Server parse and compile time:

    CPU time = 16 ms, elapsed time = 49 ms.

    Table 'T'. Scan count 1, logical reads 4238, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 62 ms, elapsed time = 85 ms.

    Table T has 804750 records

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    Adam's query

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    Table 'Worktable'. Scan count 1, logical reads 21, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'T'. Scan count 2, logical reads 8476, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 1763 ms, elapsed time = 1773 ms.

    Naomi's query

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'T'. Scan count 5, logical reads 21190, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 4930 ms, elapsed time = 6379 ms.

    Alejandro's query

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'T'. Scan count 2, logical reads 8476, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 3806 ms, elapsed time = 4010 ms.

    Packed value solution

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'T'. Scan count 1, logical reads 4238, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 1264 ms, elapsed time = 1312 ms.

    I've also updated blogs with more information.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, April 30, 2010 3:47 AM
    Moderator
  • In my understanding we wanted to get latest date record and in case of a tie the record with the greatest UniqueID. Adam's solution doesn't solve this problem. Your latest variation does. See also the performance tests I made. In my tests packed value solution was a vinner.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, April 30, 2010 4:26 AM
    Moderator
  • In my understanding we wanted to get latest date record and in case of a tie the record with the greatest UniqueID. Adam's solution doesn't solve this problem. Your latest variation does. See also the performance tests I made. In my tests packed value solution was a vinner.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog


    Naom,

    I returned the max status because the unique id was not factored into the original expected results.  Also the OP never said he wanted the max or min unique Id.  This was an assumption. 

    If the OP does in fact want the max or min UniqueId you would have to convert my current solution into another derived table (that gets the min uniqueid) and join back onto @t where the join expression joins by uniqueid.  This will probably increase the number of reads, but is very simple and straightforward.


    http://jahaines.blogspot.com/
    Friday, April 30, 2010 12:12 PM
    Moderator
  • Adam,

    Can you post it? I think I see what you mean, but just in case.

    It would be nice if OP can clarify the requirement, but I got it as

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

    Give the latest date per LoginID/Desc and in case of the tie give the record with the latest UniqueID.

    I added Alejandro's solution into the blogs I mentioned in the begining of the thread.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, April 30, 2010 1:23 PM
    Moderator
  • The OP did say he wanted the latest UniqueID:

    "Thanks Brad, yes it should get the latest UniqueId in that case. I am trying to run this code but the environment I am using doesnt seem like accepting "WITH cte AS" (i tried with and without the semicol) it produces a syntax error."


    --Brad (My Blog)
    Friday, April 30, 2010 1:37 PM
    Moderator
  • Naom,

    Here are two solutions.  One is the one I spoke of earlier and the other is another method that is commonly used for multiple paritions.

    Edit: I just saw you did a similar query Naom, but called it packed value, but oh well I guess we can have too similar queries :)

    --This indexes help both queries
    --CREATE NONCLUSTERED INDEX ncl_idx ON dbo.T(logdate,loginid)INCLUDE ([DESC],[Status])
    --CREATE NONCLUSTERED INDEX ncl_idx2 ON dbo.T(loginid,logdate)INCLUDE ([DESC],[Status])
    
    --Solution 1 - Another Aggregate Parition
    SELECT t.LoginId,t.[Desc],t.[Status],t.LogDate
    FROM dbo.t
    INNER JOIN(
    	SELECT MAX(UniqueID) AS UniqueID
    	FROM dbo.t
    	INNER JOIN(
    		SELECT MAX(LogDate) AS LogDate,LoginId, [DESC]
    		FROM t t
    		GROUP BY LoginId, [DESC]
    	) AS t2
    	ON t.[LogDate] = t2.[LogDate] 
    		AND t.[LoginID] = t2.LoginId
    		AND [t].[DESC] = [t2].[DESC]
    	GROUP BY 
    		t.LoginId,
    		t.[Desc],
    		t.[LogDate]
    ) AS t2
    ON t.[UniqueID] = t2.[UniqueID]
    
    --Solution 2 - Build a Unique Key
    SELECT t.LoginId,t.[Desc],t.[Status] AS [Status],t.LogDate
    FROM dbo.t
    INNER JOIN(
    	SELECT MAX(CAST(LogDate AS Varbinary(16)) + CAST([UniqueID] AS varbinary(16))) AS UnqKey,LoginId, [DESC]
    	FROM dbo.t
    	GROUP BY LoginId, [DESC]
    ) AS t2
    ON CAST(t.LogDate AS Varbinary(16)) + CAST(t.[UniqueID] AS varbinary(16)) = t2.[UnqKey] 
    	AND t.[LoginID] = t2.LoginId
    	AND [t].[DESC] = [t2].[DESC]
    Friday, April 30, 2010 5:16 PM
    Moderator
  • Adam,

    Very strange - I wrote a message and now I don't see it :( Did someone delete my last reply for any reason?

    I wanted to say that I'm running tests now, but the original second version of Adam's query never finished - in a few minutes I'm going to cancel it. I doubt varbinary will speed it up since it's used in JOINs.

    My packed query is not the same as yours...


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    I wasnt stating that the queries were the same.  I was stating the logic is similar.  You packed more of the data into the key and parsed it out, which will definitely cut down on the number of reads.  I was posting how a typical key packing works ,where you typically pack keys or groupings together and not necesarily all attributes.

    http://jahaines.blogspot.com/
    Friday, April 30, 2010 6:03 PM
    Moderator
  • Looks like it was not your query being a culprit. I added the indexes as you suggested, and here is what I got:

    SQL Server parse and compile time:

    CPU time = 62 ms, elapsed time = 290 ms.

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 631 ms.

    Table 'T'. Scan count 1, logical reads 4237, physical reads 17, read-ahead reads 4237, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 2949 ms, elapsed time = 8491 ms.

    SQL Server Execution Times:

    CPU time = 2964 ms, elapsed time = 9323 ms.

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 1 ms.

    Table 'T'. Scan count 1, logical reads 3941, physical reads 23, read-ahead reads 3674, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 1966 ms, elapsed time = 7146 ms.

    SQL Server Execution Times:

    CPU time = 1966 ms, elapsed time = 7148 ms.

    Table 'T'. Scan count 1, logical reads 3942, physical reads 37, read-ahead reads 3719, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 78 ms, elapsed time = 1067 ms.

    Table T has 804750 records

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 1 ms.

    Adam's query

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    Table 'T'. Scan count 5, logical reads 6705, physical reads 224, read-ahead reads 6488, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 1060 ms, elapsed time = 3020 ms.

    Adam's query 2 using compound key to JOIN

    Table 'T'. Scan count 5, logical reads 11834, physical reads 11, read-ahead reads 3608, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 1295 ms, elapsed time = 1763 ms.

    Naomi's query

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'T'. Scan count 5, logical reads 157292, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 2933 ms, elapsed time = 4136 ms.

    Alejandro's query

    Table 'Worktable'. Scan count 721278, logical reads 14882717, physical reads 1017, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'T'. Scan count 2, logical reads 7884, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 1531571 ms, elapsed time = 1628957 ms.

    Why indexes decreased the performance of that query so much???

    Packed value solution

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'T'. Scan count 1, logical reads 3942, physical reads 3, read-ahead reads 3923, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 1404 ms, elapsed time = 1969 ms.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, April 30, 2010 6:10 PM
    Moderator
  • And this was my test program (last night I ran it several times, that's why I have the number of records as I have - you can adjust it to use a single loop to create big tables).

    -- Test query
    /*
    create TABLE T(
    UniqueID INT identity primary key,
    LoginID VARCHAR(10),
    [DESC] VARCHAR(10),
    [Status] VARCHAR(10),
    LogDate DATETIME
    );*/
    
    set nocount on
    
    declare @i int = 0
    while @i < 10000
    begin
    INSERT INTO T VALUES ('ABC1','Descr1','GOOD','1/1/2010'),
     ('ABC1','Descr2','GOOD','1/1/2010'),
     ('ABC1','Descr1','BAD','2/2/2010'),
     ('ABC1','Descr1','OK','3/3/2010'),
    ('DEF1','Descr1','NOT BAD','4/4/2010'),
    ('DEF1','Descr1','BAD','4/4/2010'),
    ('DEF1','Descr2','BAD','5/5/2010'),
    ('DEF1','Descr2','GOOD','5/5/2010'),
    ('ABC1','Descr1','BAD','2/2/2010'),
    ('ABC1','Descr1','OK','3/2/2010')
     set @i = @i+ 1
    end
    go
    CREATE NONCLUSTERED INDEX ncl_idx ON dbo.T(logdate,loginid)INCLUDE ([DESC],[Status])
    CREATE NONCLUSTERED INDEX ncl_idx2 ON dbo.T(loginid,logdate)INCLUDE ([DESC],[Status])
    
    declare @cnt int
    select @cnt =COUNT(*) from T
    print 'Table T has ' + cast(@cnt as varchar(10)) + ' records'
    
    print 'Adam''s query'
    set statistics io on
    set statistics time on
    --Solution 1 - Another Aggregate Parition
    SELECT t.LoginId,t.[Desc],t.[Status],t.LogDate
    FROM dbo.t
    INNER JOIN(
    	SELECT MAX(UniqueID) AS UniqueID
    	FROM dbo.t
    	INNER JOIN(
    		SELECT MAX(LogDate) AS LogDate,LoginId, [DESC]
    		FROM t t
    		GROUP BY LoginId, [DESC]
    	) AS t2
    	ON t.[LogDate] = t2.[LogDate] 
    		AND t.[LoginID] = t2.LoginId
    		AND [t].[DESC] = [t2].[DESC]
    	GROUP BY 
    		t.LoginId,
    		t.[Desc],
    		t.[LogDate]
    ) AS t2
    ON t.[UniqueID] = t2.[UniqueID]
    
    set statistics time off	
    set statistics io off
    
    print 'Adam''s query 2 using compound key to JOIN'	
    set statistics io on
    set statistics time on
    
    --Solution 2 - Build a Unique Key
    SELECT t.LoginId,t.[Desc],t.[Status] AS [Status],t.LogDate
    FROM dbo.t
    INNER JOIN(
    	SELECT MAX(CAST(LogDate AS FLOAT) + [UniqueID]) AS UnqKey,LoginId, [DESC]
    	FROM dbo.t
    	GROUP BY LoginId, [DESC]
    ) AS t2
    ON CAST(t.LogDate AS FLOAT) + t.[UniqueID] = t2.[UnqKey] 
    	AND t.[LoginID] = t2.LoginId
    	AND [t].[DESC] = [t2].[DESC]
    
    set statistics time off	
    set statistics io off	
    print 'Naomi''s query'
    set statistics io on
    set statistics time on
    SELECT UniqueID, LoginID, [Desc], [Status], LogDate
    FROM T where UniqueID = 
    (select top 1 UniqueID from T T1 where T1.LoginID = T.LoginID and 
    T1.[Desc] = T.[Desc] order by LogDate DESC, UniqueID DESC) 
    
    set statistics time off
    set statistics io off
    
    
    print 'Alejandro''s query'
    set statistics io on
    set statistics time on
    SELECT UniqueID, LoginID, [Desc], [Status], LogDate
    FROM
     T AS T1
    WHERE
     NOT EXISTS (
     SELECT *
     FROM T AS T2
     WHERE
     T2.LoginID = T1.LoginID
     AND T2.[DESC] = T1.[DESC]
     AND (
     T1.LogDate < T2.LogDate
     OR (T1.LogDate = T2.LogDate AND T1.UniqueID < T2.UniqueID)
     )
     ) 
     set statistics time off
     set statistics io off
     print 'Packed value solution'
     
     set statistics io on
     set statistics time on
     
    SELECT cast(substring(Compound,11,10) as int) as UniqueID, LoginID, [Desc], cast(right(Compound,10) as varchar(10)) as [Status], LogDate
    FROM (select max(LogDate) as LogDate, 
    max(convert(binary(10),LogDate,112)+ CAST(UniqueID as binary(10)) + cast([Status] as binary(10))) as Compound,
    [Desc], [LoginID] from T group by [LoginID],[Desc]) X --order by LoginID, [Desc], UniqueID
    
    /* Variation without derived table turned out to be slower
    select max(LogDate) as LogDate, 
    cast(substring(max(convert(binary(10),LogDate,112)+ CAST(UniqueID as binary(10))),11,10) as int) as UniqueID ,
    cast(right(max(convert(binary(10),LogDate,112)+ CAST(UniqueID as binary(10)) + cast([Status] as binary(10))),10) as varchar(10)) as [Status],
    [Desc], [LoginID] from T group by [LoginID],[Desc] order by LoginID, [Desc], UniqueID
    */

    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, April 30, 2010 6:11 PM
    Moderator
  • It seems that there are a lot of ways to do this.  Here is one additional way that uses MAX() on the UniqueID to find the latest record on the same day.  If you copy / paste the SQL, it will return two result sets that match the ones you provided in your initial question (with only 4 records in the final result set).

    create table #temp (UniqueID int, LoginID char(4), [Desc] char(6), Status varchar(4), LogDate datetime)
    insert into #temp select 1, 'ABC1', 'Descr1', 'GOOD', '1/1/2010'
    insert into #temp select 2, 'ABC1', 'Descr2', 'GOOD', '1/1/2010'
    insert into #temp select 3, 'ABC1', 'Descr1', 'BAD', '2/2/2010'
    insert into #temp select 4, 'ABC1', 'Descr1', 'OK',  '3/3/2010'
    insert into #temp select 5, 'DEF1', 'Descr1', 'BAD', '4/4/2010'
    insert into #temp select 6, 'DEF1', 'Descr2', 'BAD', '5/5/2010'
    insert into #temp select 7, 'DEF1', 'Descr2', 'GOOD', '5/5/2010'
    
    select * from #temp
    
    select t.LoginID, t.[Desc], t.Status, t.LogDate 
    from  #temp t inner join (
           select LoginID, [Desc], max(UniqueID) as MaxID 
           from #temp 
           group by LoginID, [Desc]
        ) as z on t.UniqueID = z.MaxID
    
    drop table #temp

     

    I read through a lot of the other posts.  I didn't find anything that said the question was any different from your intial post.  Does this accomplish what you are trying to do?

    -- JimVern

    Friday, April 30, 2010 6:18 PM
  • If the UniqueID is not in the chronological order, then this solution is not going to work - that's the point.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, April 30, 2010 6:25 PM
    Moderator
  • Ok, I removed Alejandro's query from the tests and changed Adam's second to a new revision.

    Here is what I got:

    SQL Server parse and compile time:

    CPU time = 3245 ms, elapsed time = 7365 ms.

    Table 'T'. Scan count 1, logical reads 4939, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 94 ms, elapsed time = 102 ms.

    Table T has 1004750 records

    SQL Server Execution Times:

    CPU time = 15 ms, elapsed time = 1 ms.

    Adam's query

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    Table 'T'. Scan count 5, logical reads 8413, physical reads 19, read-ahead reads 2682, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 1264 ms, elapsed time = 2003 ms.

    Adam's query 2 using compound key to JOIN

    Table 'T'. Scan count 5, logical reads 14831, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 2480 ms, elapsed time = 2686 ms.

    Naomi's query

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'T'. Scan count 5, logical reads 196382, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 3932 ms, elapsed time = 6143 ms.

    Packed value solution

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'T'. Scan count 1, logical reads 4939, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 1778 ms, elapsed time = 1844 ms.

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

    The last packed value solution still seems to be a winner.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, April 30, 2010 6:28 PM
    Moderator
  • It seems that there are a lot of ways to do this.  Here is one additional way that uses MAX() on the UniqueID to find the latest record on the same day.  If you copy / paste the SQL, it will return two result sets that match the ones you provided in your initial question (with only 4 records in the final result set).

    create table #temp (UniqueID int, LoginID char(4), [Desc] char(6), Status varchar(4), LogDate datetime)
    
    insert into #temp select 1, 'ABC1', 'Descr1', 'GOOD', '1/1/2010'
    
    insert into #temp select 2, 'ABC1', 'Descr2', 'GOOD', '1/1/2010'
    
    insert into #temp select 3, 'ABC1', 'Descr1', 'BAD', '2/2/2010'
    
    insert into #temp select 4, 'ABC1', 'Descr1', 'OK', '3/3/2010'
    
    insert into #temp select 5, 'DEF1', 'Descr1', 'BAD', '4/4/2010'
    
    insert into #temp select 6, 'DEF1', 'Descr2', 'BAD', '5/5/2010'
    
    insert into #temp select 7, 'DEF1', 'Descr2', 'GOOD', '5/5/2010'
    
    
    
    select * from #temp
    
    
    
    select t.LoginID, t.[Desc], t.Status, t.LogDate 
    
    from #temp t inner join (
    
        select LoginID, [Desc], max(UniqueID) as MaxID 
    
        from #temp 
    
        group by LoginID, [Desc]
    
      ) as z on t.UniqueID = z.MaxID
    
    
    
    drop table #temp
    
    

     

    I read through a lot of the other posts.  I didn't find anything that said the question was any different from your intial post.  Does this accomplish what you are trying to do?

    -- JimVern


    Jim,

    I dont believe this solution will give the correct result.  You are grabbing the max UniqueID; however, the OP wanted the LogDate to be the deciding factor of what is "new".  I would suspect that it is possible to have a greater date, on a lesser UnqiueId.  If we can attribute 'newer" records by Id this will save us a lot of IO and increase performance, but I dont think this is the case here.


    http://jahaines.blogspot.com/
    Friday, April 30, 2010 6:42 PM
    Moderator
  • If the UniqueID is not in the chronological order, then this solution is not going to work - that's the point.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog


    Naom, thanks for clarifying.  I tried an adapted my query using a slightly different approach.  The UniqueID is the same for all records to demonstrate that we don't need to use that value.  I copied your load test and ran it against this query and achieved some fairly low CPU and IO results with a single table scan, very simliar to your packed value solution, though not quite as fast (~30ms slower).  I'll post my modification here, then follow it up with an adaptation of your load test with those results.

    create table #temp (UniqueID int, LoginID char(4), [Desc] char(6), Status varchar(4), LogDate datetime)
    insert into #temp select 1, 'ABC1', 'Descr1', 'GOOD', '1/1/2010'
    insert into #temp select 1, 'ABC1', 'Descr2', 'GOOD', '1/1/2010'
    insert into #temp select 1, 'ABC1', 'Descr1', 'BAD', '2/2/2010'
    insert into #temp select 1, 'ABC1', 'Descr1', 'OK', '3/3/2010'
    insert into #temp select 1, 'DEF1', 'Descr1', 'BAD', '4/4/2010'
    insert into #temp select 1, 'DEF1', 'Descr2', 'BAD', '5/5/2010'
    insert into #temp select 1, 'DEF1', 'Descr2', 'GOOD', '5/5/2010'
    insert into #temp select 1, 'DEF1', 'Descr2', 'GOOD', '5/5/2010'
    
    select * from #temp
    
    select 
     y.LoginID, y.[Desc], 
     case y.TempID % 1 * 10 
      when 3 then 'GOOD' 
      when 2 then 'OK' 
      else 'BAD' 
     end as Status, 
     dateadd(dd, cast(y.TempID as int), '1/1/1990') as LogDate 
    from (
     select x.LoginID, x.[Desc], max(x.TempID) as TempID 
     from (
      select 
       LoginID, [Desc], 
       cast(datediff(dd, '1/1/1990', LogDate) as decimal(10,1)) + 
        case Status 
         when 'Good' then .3 
         when 'Ok' then .2 
         else .1 
        end as TempID,
       max(cast(UniqueID as char(36))) as MaxID 
      from #temp 
      group by 
       LoginID, [Desc], [Status], 
       cast(datediff(dd, '1/1/1990', LogDate) as decimal(10,1)) + 
        case Status 
         when 'Good' then .3 
         when 'Ok' then .2 
         else .1 
        end
     ) as x
     group by x.LoginID, x.[Desc]
    ) as y
    order by 4, 1, 2
    go
    drop table #temp

    It

    Friday, April 30, 2010 7:57 PM
  • Here is the results from running your load test, including my math oriented query:

    Table T has 100000 records
    Adam's query
    Table 'T'. Scan count 5, logical reads 866, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    
    SQL Server Execution Times:
      CPU time = 141 ms, elapsed time = 141 ms.
    Adam's query 2 using compound key to JOIN
    Table 'T'. Scan count 5, logical reads 1497, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    
    SQL Server Execution Times:
      CPU time = 156 ms, elapsed time = 148 ms.
    Naomi's query
    Table 'T'. Scan count 7, logical reads 21078, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Worktable'. Scan count 2, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    
    SQL Server Execution Times:
      CPU time = 358 ms, elapsed time = 248 ms.
    Alejandro's query
    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'T'. Scan count 2, logical reads 990, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    
    SQL Server Execution Times:
      CPU time = 610 ms, elapsed time = 614 ms.
    Packed value solution
    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'T'. Scan count 1, logical reads 495, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    
    SQL Server Execution Times:
      CPU time = 156 ms, elapsed time = 157 ms.
    JimVern's query
    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'T'. Scan count 1, logical reads 495, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    
    SQL Server Execution Times:
      CPU time = 188 ms, elapsed time = 186 ms.
    
    Friday, April 30, 2010 8:00 PM
  • I also tried running it without any indexes (the primary index on the UniqueID identity column or the other two non-clustered indexes and achieved almost identical results.

    JimVern's query
    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'T'. Scan count 1, logical reads 552, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    
    SQL Server Execution Times:
      CPU time = 188 ms, elapsed time = 191 ms.
    
    Friday, April 30, 2010 8:08 PM
  • Yes.  You are correct.  I didn't catch that in the longer threads...  I changed the query though to exclude the UniqueID altogther and it still performs very nicely with only one table scan:

    create table #temp (UniqueID int, LoginID char(4), [Desc] char(6), Status varchar(4), LogDate datetime)
    insert into #temp select 1, 'ABC1', 'Descr1', 'GOOD', '1/1/2010'
    insert into #temp select 1, 'ABC1', 'Descr2', 'GOOD', '1/1/2010'
    insert into #temp select 1, 'ABC1', 'Descr1', 'BAD', '2/2/2010'
    insert into #temp select 1, 'ABC1', 'Descr1', 'OK', '3/3/2010'
    insert into #temp select 1, 'DEF1', 'Descr1', 'BAD', '4/4/2010'
    insert into #temp select 1, 'DEF1', 'Descr2', 'BAD', '5/5/2010'
    insert into #temp select 1, 'DEF1', 'Descr2', 'GOOD', '5/5/2010'
    insert into #temp select 1, 'DEF1', 'Descr2', 'GOOD', '5/5/2010'
    
    select * from #temp
    
    select 
     y.LoginID, y.[Desc], 
     case y.TempID % 1 * 10 
      when 3 then 'GOOD' 
      when 2 then 'OK' 
      else 'BAD' 
     end as Status, 
     dateadd(dd, cast(y.TempID as int), '1/1/1990') as LogDate 
    from (
     select x.LoginID, x.[Desc], max(x.TempID) as TempID 
     from (
      select 
       LoginID, [Desc], 
       cast(datediff(dd, '1/1/1990', LogDate) as decimal(10,1)) + 
        case Status 
         when 'Good' then .3 
         when 'Ok' then .2 
         else .1 
        end as TempID
      from #temp 
      group by 
       LoginID, [Desc], [Status], 
       cast(datediff(dd, '1/1/1990', LogDate) as decimal(10,1)) + 
        case Status 
         when 'Good' then .3 
         when 'Ok' then .2 
         else .1 
        end
     ) as x
     group by x.LoginID, x.[Desc]
    ) as y
    order by 4, 1, 2
    go
    drop table #temp
    Friday, April 30, 2010 9:49 PM