0

I have a SQL Server Script that works but I would like to add one more feature. Finally, I would like to select the top row (max date) per group. I've managed to create row numbers (rn) per group and attempted adding in the code WHERE rn=1 in several locations but always get errors. Is there a way I can select the top row per group (NewTab.sid) either by using the row numbers or some other means?

SELECT NewTab.sid,
       NewTab.pid,
       NewTab.edate,
       NewTab.codeid,
       NewTab.SStat,
       p.BaseStart,
       p.BaseEnd,
       ROW_NUMBER() OVER (PARTITION BY NewTab.sid ORDER BY NewTab.edate DESC) AS rn
FROM PrTab p
     INNER JOIN
     -- first set
     (SELECT c.sid,
             c.pid,
             c.edate,
             c.codeid,
             'NS' AS Status
      FROM ClTab c
      WHERE c.codeid IN ('var1', 'var2')
      UNION
      -- second set
      SELECT c.sid,
             c.pid,
             c.edate,
             c.codeid,
             'XS' AS Status
      FROM ClTab c
      WHERE c.codeid IN ('var3', 'var4')
      UNION
      -- third set
      SELECT c.sid,
             c.pid,
             c.edate,
             c.codeid,
             'SM' AS Status
      FROM ClTab c
      WHERE c.codeid IN ('var5', 'var6')) NewTab ON p.pid = NewTab.pid
WHERE NewTab.edate < p.BaseEnd
ORDER BY NewTab.sid ASC,
         NewTab.edate DESC;
Thom A
  • 88,727
  • 11
  • 45
  • 75
Jim Maas
  • 1,481
  • 2
  • 16
  • 36
  • 1
    Please show sample data and desired results. And actually show what you tried with the error you received. – Dale K Jan 21 '22 at 09:08
  • 1
    See [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group). Note most declared the `ROW_NUMBER` in a derived table, and then reference it's value outside of it. If you don't want to do that, use the `TOP (1) WITH TIES` method. – Thom A Jan 21 '22 at 09:11
  • Apologies, have seen that other solution but didn't realise it would work in my case. It does! Thx. – Jim Maas Jan 21 '22 at 10:55

0 Answers0