CODEWITHSUNDEEP
sql-server
Kevin
Kevin

Reputation: 338

SQL - get top record for each date

There is always 1 to 31 different dates which is returned in this query (depending on when in the month it is ran) and there can be multiples of the same date.
I would like to select the top 1 record for each date returned in my query. Can someone show me how to do this?

Here is my query:

SELECT      
currentDate
,month(currentDate) month
,DATENAME(month, currentDate) as 'MonthName'
,DATEPART(wk,currentDate) week
,LEFT(CAST(DATEPART(YEAR,currentDate) AS CHAR),4) + 
RIGHT('0' + CAST(datepart (week,currentDate) AS VARCHAR(2)),2) AS Yearweek
,RTMCode
,RTM
,CPCode
,CP
,CDCode
,CD
,Branded
,RV
,Holiday
FROM dbo.EDB

Upvotes: 0

Views: 104

Answers (4)

Karl Kieninger
Karl Kieninger

Reputation: 9149

I'm not clear how you are defining top, so max may not be flexible enough for you. I'm also assuming you have some sort of primary key on the table.

WITH cte AS (
  SELECT pk
        ,ROW_NUMBER OVER(PARTION BY currentDate ORDER BY [whatever]) rn
    FROM EDB
)
SELECT a.*
  FROM EDB a
       INNER JOIN
       cte b ON a.pk = b.pk
 WHERE b.rn = 1

Upvotes: 0

Afroz
Afroz

Reputation: 1102

Use CROSS APPLY. Assuming CurrentDate is DATE column, here's an example. You have to change ORDER BY to either ID or CurrentDate if currentdate is datetime.

SELECT t2.currentDate
,month(t2.currentDate) month
,DATENAME(month, t2.currentDate) as 'MonthName'
,DATEPART(wk,t2.currentDate) week
,LEFT(CAST(DATEPART(YEAR,t2.currentDate) AS CHAR),4) + 
RIGHT('0' + CAST(datepart (week,t2.currentDate) AS VARCHAR(2)),2) AS Yearweek
,RTMCode
,RTM
,CPCode
,CP
,CDCode
,CD
,Branded
,RV
,Holiday
FROM
 (SELECT currentDate
 FROM dbo.EDB e
 GROUP BY currentDate)t
CROSS APPLY ( SELECT TOP 1 * FROM dbo.EDB i1 
           WHERE i1.currentDate = t.CurrentDate 
           ORDER BY i1.currentDate DESC)t2

Upvotes: 1

JBrooks
JBrooks

Reputation: 10013

If I understand, I think you want something like:

SELECT *
FROM dbo.EDB
WHERE ID IN 
   (SELECT MAX(ID) AS MaxID
    FROM dbo.EDB
    WHERE MONTH(currentDate) = @month
    AND YEAR(currentDate) = @year
    GROUP BY convert(varchar, currentDate, 101))
ORDER BY currentDate

Upvotes: 0

Erran Morad
Erran Morad

Reputation: 4753

I don't know what exactly you want. Is this what you want -

Sample table -

id  dates
2   2014-03-01
1   2014-03-01
3   2014-03-01
5   2014-03-02
6   2014-03-02

Query - 

select MAX(id) as TopZ, dates
from datings 
group by dates
order by dates asc

Result - 

TopZ  dates
3     2014-03-01
6     2014-03-02

Upvotes: 0

Related Questions