CODEWITHSUNDEEP
sqlsql-server
Zach
Zach

Reputation: 650

Selecting first entry per day

My table will be structured like this

temp
ID | Date
---|-----------
1  | 2018-01-01
2  | 2018-01-01
3  | 2018-01-01
4  | 2018-01-02
5  | 2018-01-02
6  | 2018-01-03

And I will have an input from the user for start and end dates:

@StartDate DATE = '2018-01-01'
@EndDate DATE = '2018-01-03'

And I want my return structured like so:

ID | Date
---|-----------
1  | 2018-01-01
4  | 2018-01-02
6  | 2018-01-03

I've tried doing this:

select distinct temp.ID, joinTable.Date
from temp
inner join (
            select min(innerTemp.Date), innerTemp.ID
            from temp innerTemp
            where innerTemp.Date >= @StartDate
            and innerTemp.Date < @EndDate
            group by innerTemp.ID, innerTemp.Date
           ) as joinTable on joinTable.ID = temp.ID and joinTable.Date = temp.Date
where temp.Date >= @StartDate
and temp.Date < @EndDate
order by temp.Date desc

To try to join the table to itself with only one entry per day then choose from that but that isn't working. I am pretty stumped on this one. Any ideas?

Upvotes: 2

Views: 54

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

That seems very complicated. This returns the result set you want:

select min(id), date
from temp
where date >= @StartDate and date < @EndDate
group by date;

If you have other columns you want to keep (so group by is not appropriate), a simple method with good performance is:

select t.*
from temp t
where t.id = (select min(t2.id) from temp t2 where t2.date = t.date and t2.date >= @StartDate and t2.date < @EndDate);

Of course, you can also use row_number(), but with an index on temp(date, id) and temp(id), the above should be pretty fast.

Upvotes: 3

LocN
LocN

Reputation: 41

WITH cte AS 
( 
    SELECT 
        *
        , ROW_NUMBER() OVER(PARTITION BY date ORDER BY id asc) rn
    FROM 
        temp ) 
SELECT 
    id,
    date
FROM 
    rn = 1

Upvotes: 2

Related Questions