What I am tryint to do is basically have a list of events. On each events there are multiple criterias and several keys. I want to retrieve the lates record of each unique key with the value that that specific row has. What I have tried:
SELECT max(b.event_time) AS 'tidpunkt'
,b.sin
,a.ino
INTO #timer_remove_whom_all
FROM #int a
JOIN log b ON a.sin = b.sin
WHERE a.deal_id = 'PSE'
AND convert(DATE, dateadd(second, b.event_time, '800101'), 102) = convert(DATE, getdate() - 2, 102)
GROUP BY b.sin
,a.ino
Returns:
tidpunkt sin ino
1212649647 3566454 554
This retrieves the specific record that I want. But in this case I need another param from that row such as terminal, in this case.
But if I use the b.terminal in the select, I have to group it, and at that point It wont give me the actual correct terminal, but grabs the wrong one.
So the question here is, how do i actually make it take the b.terminal that belongs to the row that i have used max(b.event_time) on previously?
What I have:
tidpunkt sin ino terminal
1212649647 3566454 554 bandy
1212642065 3566454 554 hockey
What I want to return:
tidpunkt sin ino terminal
1212649647 3566454 554 bandy
What gets returned:
tidpunkt sin ino terminal
1212649647 3566454 554 hockey
Notice how the terminal doesnt belong to the rest of it. Thereby my group by isnt the way to go. I would honestly hope that there is some kind of way to get the related record to whichever aggregate function that is used but I cant go further alone at this point
You want row_number():
SELECT . . .
INTO #timer_remove_whom_all
FROM (SELECT . . .,
ROW_NUMBER() OVER (PARTITION BY l.sin, i.ino ORDER BY l.event_time DESC) as seqnum
FROM #int i JOIN
log l
ON i.sin = l.sin
WHERE i.deal_id = 'PSE' AND
convert(DATE, dateadd(second, l.event_time, '800101'), 102) = convert(DATE, getdate() - 2, 102)
) il
WHERE seqnum = 1;
The . . . is for the columns that you want. Note that I also changed the table aliases to be abbreviations of the table names.