-2

I'm very new to T-SQL so sorry if this is not right.

Using T-SQL, how do I pull the max number from a record using 2 other columns? I have a unique ID number, visit number per ID and then the number of rooms that person have been to (Can do multiple rooms in one visit). How do I get it so that I can only select the row which has the highest number of Rooms a person visits using the persons ID and Visit Number?

Let's say I have this table;

enter image description here

What I want to have is just the rows with the highest Room number per ID and Visit_No. So with this table I would want to retrieve this dataset;

Dataset Wanted:

enter image description here

I thought using a ROW_SEQUENCE would work but I have tried but I don't think I am doing it correctly.

SeanLearningSQL
  • 1
  • 1

1 Answers1

1

This can be done using group by and max():

First get the latest Room_No per ID and Visit No

select ID, Visit_No, max(Room_No) as Room_No
from mytable
group by ID, Visit_No

Then get the room name using inner join

select t.*
from mytable t
inner join (
  select ID, Visit_No, max(Room_No) as max_Room_No
  from mytable
  group by ID, Visit_No
) as s on s.ID = t.ID and s.Visit_No = t.Visit_No and s.max_Room_No = t.Room_No
SelVazi
  • 10,028
  • 2
  • 13
  • 29