Select only first 2 records for each group
Clash Royale CLAN TAG#URR8PPPSelect only first 2 records for each group
My current result is given below for a particular memberid.
MemberID ResNumber pcode MemberEmail arrivaldate
601315522 33350554 CAAUBU rjj@gmail.com 7/11/2018
601315522 33350566 CAAUBU rjj@gmail.com 7/11/2018
601315522 33350574 CAAUBU rjj@gmail.com 7/11/2018
601315522 33354848 CAAUBU rjj@gmail.com 7/11/2018
601315522 33355136 CAAUBU rjj@gmail.com 7/11/2018
601315522 33356364 CAAUBU rjj@gmail.com 7/11/2018
I want be able to select first 2 records for every MemberId, some MemberId would have 10 records and some may have 100 but i just want the first 2 records and reject the other ones.
That's the output i am expecting:
MemberID ResNumber pcode MemberEmail arrivaldate
601315522 33350554 CAAUBU rjj@gmail.com 7/11/2018
601315522 33350566 CAAUBU rjj@gmail.com 7/11/2018
row_number()
How do you define the first two? What are you ordering by?
– Mureinik
yesterday
I would order it by arrival date, but these reservations have the same arrival day and i want to be able to just select 2 ResNumber to give them rewards on a particular arrival date.
– Sanky
yesterday
Microsoft SQL Azure (RTM) - 12.0.2000.8
– Sanky
yesterday
1 Answer
1
Here is how to do it with Row Number
SELECT MemberID, ResNumber, pcode, MemberEmail, arrivaldate,
FROM (
SELECT MemberID, ResNumber, pcode, MemberEmail, arrivaldate,
ROW_NUMBER () OVER w AS RN
FROM sometable
WINDOW w AS (PARTITION BY MemberID ORDER BY ResNumber ASC)
) X
WHERE RN <= 2
It says incorrect syntax near 'w'.
– Sanky
yesterday
@Sanky you marked this as correct, did you figure out what my typo was?
– Hogan
yesterday
Yes i fixed the typo.
– Sanky
yesterday
@sanky can you tell me what it is so I can fix it in my answer?
– Hogan
2 hours ago
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
what mysql version you have? newer version you can use
row_number()old version you need use user variables– Juan Carlos Oropeza
yesterday