SQL Server group by but select 'top' date

oxosxuxt 于 5个月前发布在 SQL Server

关注(0)|答案(5)|浏览(57)

I have a table in SQL server like so (Note the ID field is not unique):

-----------------------------------
| ID | IsAdamBrown | DateComplete |
| 1  |     TRUE    | 2017-01-01   |
| 1  |     TRUE    | 2017-01-03   |
-----------------------------------

I'd like to select one row for all the unique IDs in the table and the most recent 'DateComplete' for that ID.

My desired output in this case would be:

-----------------------------------
| ID | IsAdamBrown | DateComplete |
| 1  |     TRUE    | 2017-01-03   |
-----------------------------------

I've tried:

SELECT DISTINCT DateComplete, ID, IsAdamBrown 
  FROM thisTable
  WHERE IsAdamBrown IS NOT NULL
  GROUP BY DateComplete, ID, IsAdamBrown
  ORDER BY DateComplete DESC

Unfortunately I still get the two date rows back. In MySQL I would group by just the first two rows and the ORDER BY would make sure the DateComplete was the most recent. SQL servers requirement that the SELECT fields match the GROUP BY makes this impossible.

How can I get a single row back for each ID with the most recent DateComplete?

sql-server

来源：https://stackoverflow.com/questions/44625516/sql-server-group-by-but-select-top-date

5条答案

按热度按时间

8ftvxx2r1#

SELECT id, 
       isadambrown, 
       Max(datecomplete) AS DateComplete 
FROM   thistable 
GROUP  BY id, 
          isadambrown 
ORDER  BY Max(datecomplete) DESC

赞(0）回复(0）举报 5个月前

hrirmatl2#

You can get by GROUP BY with MAX() of DateComplete

SELECT ID, IsAdamBrown, MAX(DateComplete) AS DateComplete
  FROM thisTable
  WHERE IsAdamBrown IS NOT NULL
  GROUP BY ID, IsAdamBrown
  ORDER BY MAX(DateComplete) DESC

赞(0）回复(0）举报 5个月前

wnvonmuf3#

You can using LIMIT

SELECT ID, IsAdamBrown, DateComplete
  FROM thisTable
  WHERE IsAdamBrown IS NOT NULL
  GROUP BY ID, IsAdamBrown
  ORDER BY DateComplete LIMIT 1

赞(0）回复(0）举报 5个月前

vfh0ocws4#

You can use this. I hope it will work for you.

SELECT ID, IsAdamBrown, DateComplete
  FROM thisTable a
  WHERE DateComplete IN 
(
SELECT MAX(DateComplete) FROM thisTable b WHERE a.ID = b.ID GROUP BY b.ID
) ORDER BY DateComplete DESC

赞(0）回复(0）举报 5个月前

daolsyd05#

You can use ROW_NUMBER() for grouping according to ID and a subquery to get the only first record with recent iscomplete. This will first sort your data according to id and recent iscomplete and then the first result for all the unique IDs

SELECT X.ID, X.IsAdamBrown, X.DateComplete 
FROM ( SELECT ID, IsAdamBrown, DateComplete,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY DateComplete DESC) RN
       FROM thisTable
       WHERE IsAdamBrown IS NOT NULL    ) X
WHERE X.RN=1

赞(0）回复(0）举报 5个月前

我来回答

SQL Server group by but select 'top' date

5条答案

相关问题

热门标签

最新问答