SQL Server仅选择最新的记录 [英] SQL Server Select the most recent record only

问题描述

我有一个表格，看起来像这样：

Table1

  ID日期TermDate取消
 1 20140101 99999999 20140102 
 1 20140101 20130102 20140102 
 
 2 20140108 20130102 20140102 
 2 20140101 99999999 20140102 
 
 3 20140101 99999999 20140102 
 3 20140101 99999999 20140102 
 20140101 99999999 20140709 
  

什么我想要做的是按每个ID进行分组，并只选择最近的记录。
首先，我需要检查日期列，从那里如果记录更近，那么我不需要检查其他列，在这种情况下，它是ID＃2。

如果日期列对于相同的ID是相同的，那么我想比较取消列，以最近的那个为准，那么这个记录应该只显示，在这种情况下它是ID3。 p>

如果Date和Cancel列都相同，那么我需要比较TermDate列和最近应显示的，在这种情况下它是ID＃1。所有列都是int类型。

基本上结果应该是：

  ID日期TermDate取消
 1 20140101 99999999 20140102 
 2 20140108 20130102 20140102 
 3 20140101 99999999 20140709 
  

我不是最适合自己加入表格的人，并且真的不知道如何开始使用这个...我目前的工作是：将表格复制到Excel文件中，然后手动执行作业...
我在考虑使用CASE语句，但真的不知道如何将我的逻辑转换为SQL正确的代码，这里是我的pseducode：

<$ p $选择时间日期>日期然后选择更大的日期
CASE当日期=日期那么选择磨光机取消
否则当日期=日期和取消=取消那么选择the term TermDate END）
FROM Table1
GROUP BY ID

I don'甚至不知道如何使表格看起来像上面的case语句。

请让我知道什么是最好的方法来做到这一点......我在网上搜索没有帮助。

您可以使用 dense_rank 函数根据您的排序为一个ID内的每行分配排名，并为每个ID获取第一个排名：

 选择id，[date]，termdate，取消
（选择id，[date]，termdate，cancel，
 dense_rank（）over（由[日期] desc，取消desc，termdate desc划分的分区）从表1中排列
）X 
其中rank = 1 
  

SQL小提琴演示

I have a table tat looks like this:

Table1

ID Date       TermDate Cancel
1  20140101   99999999 20140102
1  20140101   20130102 20140102

2  20140108   20130102 20140102
2  20140101   99999999 20140102

3  20140101   99999999 20140102
3  20140101   99999999 20140102
3  20140101   99999999 20140709

What I want to do is, to group by each ID and select only the most recent record. First I need to check the Date column, from there if a record is more recent then I do not need to check the other columns, in this case, it is ID #2.

If the Date column is the same for same ID, then I want to compare the Cancel column, whichever is the most recent then that record should be shown only, in this case it is ID 3.

If both Date and Cancel columns are the same, then I need to compare the TermDate column and the most recent should be displayed, in this case it is ID #1. All columns are int type.

Basically the result should be:

ID Date       TermDate Cancel
1  20140101   99999999 20140102
2  20140108   20130102 20140102
3  20140101   99999999 20140709

I am not the best person with self join tables and really not sure how to get started with this... what I currently do is: I copy the table into Excel file and then do the job manually... I am thinking to use CASE Statement but really do not know how to convert my logic to SQL proper code, here is my pseducode:

SELECT (CASE WHEN Date > DATE THEN SELECT the greater Date 
        CASE WHEN Date = Date THEN SELECT the grater Cancel
        ELSE WHEN Date = Date AND Cancel = Cancel THEN SELECT the grater TermDate END)
FROM Table1
GROUP BY ID

I don't even know how to make the table look like the above with case statement.

Please let me know what is the best way to do this... I searched online with no help.

解决方案

You can use dense_rank function to assign a rank to each row within one ID based on your ordering and get the first one for each ID:

select id, [date], termdate, cancel from
    (select id, [date], termdate, cancel,
        dense_rank() over (partition by id order by [date] desc, cancel desc, termdate desc) rank
    from table1) X
where rank = 1

SQL Fiddle Demo

这篇关于SQL Server仅选择最新的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！