从行有最大日期的表中选择 info

我的桌子是这样的:

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

--不需要现金，只需证明表中有更多信息即可

我想得到每个唯一的组，其中的日期是最大值，检查大于0。所以回报应该是这样的:

group    date     checks
2    1/1/2013    800
1    1/3/2013    700
3    1/5/2013    200

未尝试的代码:

SELECT group,MAX(date),checks
FROM table
WHERE checks>0
GROUP BY group
ORDER BY group DESC

问题是它给了我所有的日期和支票而不仅仅是最大日期行。

使用 ms sql server 2005

524944 次浏览
小开
最佳答案 
SELECT group,MAX(date) as max_date
FROM table
WHERE checks>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>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).

小开

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.

小开

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
小开 
SELECT distinct
group,
max_date = MAX(date) OVER (PARTITION BY group), checks
FROM table

Should work.


提交答案