Select info from table where row has max date

Question

My table looks something like this   group    date      cash  checks   1    1 1 2013     0      0   2    1 1 2013     0      800   1    1 3 2013     0      700   3    1 1 2013     0      600   1    1 2 2013     0      400   3    1 5 2013     0      200   -- Do not need cash just demonstrating that table has more information in it  I want to get the each unique group where date is max and checks is greater than 0  So the return would look something like   group    date     checks   2    1 1 2013    800   1    1 3 2013    700   3    1 5 2013    200   attempted code   SELECT group MAX date  checks     FROM table     WHERE checks gt 0     GROUP BY group     ORDER BY group DESC   problem with that though is it gives me all the dates and checks rather than just the max date row   using ms sql server 2005

User · Answer

You can use a window MAX   like this   SELECT         max date   MAX date  OVER  PARTITION BY group  FROM table   to get max dates per group alongside other data   group  date      cash  checks  max date -----  --------  ----  ------  -------- 1      1 1 2013  0     0       1 3 2013 2      1 1 2013  0     800     1 1 2013 1      1 3 2013  0     700     1 3 2013 3      1 1 2013  0     600     1 5 2013 1      1 2 2013  0     400     1 3 2013 3      1 5 2013  0     200     1 5 2013   Using the above output as a derived table  you can then get only rows where date matches max date   SELECT   group    date    checks FROM     SELECT             max date   MAX date  OVER  PARTITION BY group    FROM table   AS s WHERE date   max date    to get the desired result   Basically  this is similar to  Twelfth s suggestion but avoids a join and may thus be more efficient   You can try the method at SQL Fiddle

User · Answer

SELECT group  date  checks   FROM table    WHERE checks  gt  0   GROUP BY group HAVING date   max date     should work

User · Answer

SELECT distinct   group     max date   MAX date  OVER  PARTITION BY group   checks FROM table   Should work

User · Answer

Using an in can have a performance impact  Joining two subqueries will not have the same performance impact and can be accomplished like this   SELECT     FROM  SELECT msisdn                callid                Change color                play file name                date played           FROM insert log          WHERE play file name NOT IN  Prompt1   Conclusion Prompt 1   silent           ORDER BY callid ASC  t1        JOIN  SELECT MAX date played  AS date played                FROM insert log GROUP BY callid  t2          ON t1 date played   t2 date played

User · Answer

SELECT group MAX date  as max date FROM table WHERE checks gt 0 GROUP BY group   That works to get the max date  join it back to your data to get the other columns   Select group max date checks from table t inner join   SELECT group MAX date  as max date FROM table WHERE checks gt 0 GROUP BY group a on a group   t group and a max date   date   Inner join functions as the filter to get the max record only   FYI  your column names are horrid  don t use reserved words for columns  group  date  table

[sql] Select info from table where row has max date

Examples related to sql

Examples related to sql-server-2005

Examples related to greatest-n-per-group