Select only first 2 records for each group

The name of the pictureThe name of the pictureThe name of the pictureClash Royale CLAN TAG#URR8PPP


Select 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





what mysql version you have? newer version you can use row_number() old version you need use user variables
– Juan Carlos Oropeza
yesterday




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.

-

Popular posts from this blog

Keycloak server returning user_not_found error when user is already imported with LDAP

Keycloak server returning user_not_found error when user is already imported with LDAP I am currently running a keycloak server as a service in a kubernetes cluster. I can access it perfectly with the initial keycloak admin account and used it to setup a new realm using LDAP for User Federation. The connection and queries seem to work fine as a I can search for Users and they pop up with their correct username, first name, last name and e-mail so all seemed fine. When I then log out and try to log in with a normal user account that exists in the AD and has been imported I just find get the following error: WARN [org.keycloak.events] (default task-8) type=LOGIN_ERROR, realmId=master, clientId=security-admin-console, userId=null, ipAddress=<my ip>, error=user_not_found, auth_method=openid-connect, auth_type=code, redirect_uri=http://myserver.com/auth/admin/master/console/#/realms/MyRealm, code_id=a8ca7f06-dfec-4e6e-8f6b-74eb871f71da, username=myUser So it seems that in some way wh
Read more

PHP parse/syntax errors; and how to solve them?

Image
PHP parse/syntax errors; and how to solve them? Everyone runs into syntax errors. Even experienced programmers make typos. For newcomers it's just part of the learning process. However, it's often easy to interpret error messages such as: PHP Parse error: syntax error, unexpected '{' in index.php on line 20 The unexpected symbol isn't always the real culprit. But the line number gives a rough idea where to start looking. Always look at the code context . The syntax mistake often hides in the mentioned or in previous code lines . Compare your code against syntax examples from the manual. While not every case matches the other. Yet there are some general steps to solve syntax mistakes . This references summarized the common pitfalls: Unexpected T_STRING Unexpected T_VARIABLE Unexpected '$varname' (T_VARIABLE) Unexpected T_CONSTANT_ENCAPSED_STRING Unexpected T_ENCAPSED_AND_WHITESPACE Unexpected $end Unexpected T_FUNCTION… Unexpected { { Unexpected } } Unexpe
Read more

How to scale/resize CVPixelBufferRef in objective C, iOS

Image
Clash Royale CLAN TAG #URR8PPP How to scale/resize CVPixelBufferRef in objective C, iOS I am trying to resize an image from a CVPixelBufferRef to 299x299. Ideally is would also crop the image. The original pixelbuffer is 640x320, the goal is to scale/crop to 299x299 without loosing aspect ratio (crop to center). I found code to resize a UIImage in objective c, but none to resize a CVPixelBufferRef. I have found various very complicated examples of object C many different image types, but none specifically for resizing a CVPixelBufferRef. What is the easiest/best way to do this, please include the exact code. ... I tried the answer from selton, but this did not work as the resulting type in the scaled buffer is not correct (goes into assert code), OSType sourcePixelFormat = CVPixelBufferGetPixelFormatType(pixelBuffer); int doReverseChannels; if (kCVPixelFormatType_32ARGB == sourcePixelFormat) { doReverseChannels = 1; } else if (kCVPixelFormatType_32BGRA == sourcePixelFormat) {
Read more