Getting the first value from a list of values โโin SQL
I have the following table
ID Name Activity date total time
1 AB 1/10/2015 209
1 AB 1/11/2015 1234
1 AB 1/12/2015 10
2 CD 1/10/2015 2347
2 CD 1/11/2015 0
2 CD 1/12/2015 0
2 CD 1/13/2015 5
3 EF 1/10/2015 53
3 EF 1/11/2015 14
4 XY 1/11/2015 76
I need the following result:
ID Name Activity date total time
1 AB 1/10/2015 209
2 CD 1/10/2015 2347
3 EF 1/10/2015 53
4 XY 1/11/2015 76
Basically, I need the first value for all names, I used the following query, but its value is empty for the name value
SELECT distinct(Id),
FIRST_VALUE(Name) OVER (ORDER BY Id Asc) AS Name,
FIRST_VALUE(ActivityDate) OVER (ORDER BY Id Asc) AS Date,
FIRST_VALUE(TimeInQueue) OVER (ORDER BY Id Asc) AS Totaltime
FROM Historytable
GROUP BY Id,ActivityDate,Name
+3
source to share
1 answer
Use Window function
SELECT ID,
NAME,
[Activity date],
total time
FROM (SELECT Row_number() OVER(partition BY Id ORDER BY [Activity date]) Rn,
ID,
NAME,
[Activity date],
[total time] from yourtable) A
WHERE rn = 1
or find the date min [Activity date]
on id
and join the result to the table with id
and[Activity date]
SELECT a.ID,
a.NAME,
a.[Activity date],
a.[total time]
FROM yourtable A
JOIN (SELECT Min([Activity date]) [Activity date],
ID
FROM yourtable) B
ON a.id = b.id
AND a.[Activity date] = b.[Activity date]
+3
source to share