0

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

Stoff
  • 517
  • 6
  • 22

1 Answers1

2

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • @stoff . . . What is `term`? – Gordon Linoff Jun 07 '18 at 13:08
  • Aaah, term = terminal. Sorry that It was unclear, not sure how I did that. Anywho, seems that I messed up my join there, so with some tweaks I got your sollution to work perfectly for me. Thanks for the help! – Stoff Jun 08 '18 at 18:18