HOW TO:SQL Server根据其他字段中的最大值选择不同的字段 [英] HOW TO: SQL Server select distinct field based on max value in other field
问题描述
id tmpname date_used tkt_nr
---|---------|------------------|--------|
1 | template| 04/03/2009 16:10 | 00011 |
2 | templat1| 04/03/2009 16:11 | 00011 |
5 | templat2| 04/03/2009 16:12 | 00011 |
3 | diffname| 03/03/2009 15:11 | 00022 |
4 | diffname| 03/03/2009 16:12 | 00022 |
6 | another | 03/03/2009 16:13 | NULL |
7 | somethin| 24/12/2008 11:12 | 00023 |
8 | name | 01/01/2009 12:12 | 00026 |
我想得到结果:
id tmpname date_used tkt_nr
---|---------|------------------|--------|
5 | templat2| 04/03/2009 16:12 | 00011 |
4 | diffname| 03/03/2009 16:12 | 00022 |
7 | somethin| 24/12/2008 11:12 | 00023 |
8 | name | 01/01/2009 12:12 | 00026 |
所以我要寻找的是基于datetime的最大值具有不同的tkt_nr
值(不包括NULL).
So what I'm looking for is to have distinct tkt_nr
values excluding NULL, based on the max value of datetime.
我尝试了几种选择,但总是失败
I have tried several options but always failed
SELECT *
FROM templateFeedback a
JOIN (
SELECT ticket_number, MAX(date_used) date_used
FROM templateFeedback
GROUP BY ticket_number
) b
ON a.ticket_number = b.ticket_number AND a.date_used = b.date_used
我将不胜感激.不幸的是,我需要代码与SQL Server兼容.
I would appreciate any help. Unfortunately I need the code to be compatible with SQL Server.
推荐答案
自从我发现窗口函数以来,我就停止了这种方式.经常有两个记录具有相同的时间戳记,而我在结果集中得到两个记录.这是tSQL的代码.与Oracle类似.我认为mySQL还不支持.
I've stopped doing things this way since I discovered windowing functions. Too often, there are two records with the same timestamp and I get two records in the resultset. Here's the code for tSQL. Similar for Oracle. I don't think mySQL supports this yet.
Select id, tmpname, date_used, tkt_nbr
From
(
Select id, tmpname, date_used, tkt_nbr,
rownum = Row_Number() Over (Partition by tkt_nbr Order by date_used desc)
) x
Where row_num=1
这篇关于HOW TO:SQL Server根据其他字段中的最大值选择不同的字段的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!