如何从多个记录中的每个记录获取最短日期 [英] How to get minimum date by each records from multiple records

问题描述

我想获得我的表中每个记录的最小日期，该记录具有一个主键多次输入日期.看看我的桌子:

I would like to get the minimum date of each record in my table having multiple entry of date with one primary key. Take a look at my table:

   CaseNo     Entry_date   
   ABC-001     2/12/13
   ABC-002     2/09/13
   ABC-001     1/01/13
   ABC-001     1/31/13
   ABC-002     1/01/13
   ABC-003     2/01/12
   ABC-003     2/18/13

我想要这个结果:

       CaseNo     Entry_date    Min_date
       ABC-001     2/12/13      1/01/13
       ABC-002     2/09/13      1/09/13
       ABC-001     1/01/13      1/01/13
       ABC-001     1/31/13      1/01/13
       ABC-002     1/09/13      1/09/13 
       ABC-003     2/01/12      2/01/13
       ABC-003     2/18/13      2/01/13

我想获取我的桌子上记录的每个CaseNo的最少日期.

I want to get the minimum date of each CaseNo recorded on my table.

我尝试了以下代码:

Select CaseNo,Entry_date, Min(Entry_date) as Min_date
from mytable group by CaseNo

结果是这样

   CaseNo     Entry_date    Min_date
   ABC-001     1/01/13      1/01/13
   ABC-002     1/09/13      1/09/13 
   ABC-003     2/01/12      2/01/13

代码删除没有最小日期的行.我想显示所有记录的最小日期为Min_date.

The code remove the row not having the minimum date. I want to display all records with their minimum date as Min_date.

推荐答案

尝试一下

SELECT
  CaseNo,
  Entry_date,
  (SELECT MIN(Entry_date) FROM Cases subc WHERE subc.CaseNo=c.CaseNo GROUP BY CaseNo) AS MinEntryDate
FROM Cases c

这篇关于如何从多个记录中的每个记录获取最短日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！