SQL SELECT GROUP BY - get First or Last / Min or Max value

I need to create SQL that should SELECT unique values in multiple columns using GROUP BY or DISTINCT. Then I need to get the first or last related value from different column. How can I get the minimum or maximum values from related columns ?
11 Aug 2021 at 11:14 AM
0give a positive ratinggive a negative ratingshow more
LarsLars
Hi,

To get unique values using DISTINCT or GROUP BY and multiple related columns, you can try to use the solution where you LEFT JOIN the same table.

For example, when there is a table Users and you need to get unique User Groups + ID of the latest registered User for each User Group:

SELECT u1.user_group, u2.user_id, u2.user_registration_date FROM users AS u1 LEFT JOIN users AS u2 ON ( u2.user_id = (SELECT u3.user_id FROM users AS u3 WHERE u3.user_group = u1.user_group AND u3.user_status = 1 ORDER by user_registration_date DESC LIMIT 1) ) WHERE u1.user_status = 1;
Whether you need the first, last, minimum or maximum related values, you can specify it in a subquery.
22 Aug 2021 at 10:24 AM
0give a positive ratinggive a negative ratingshow more
TimTim
Share on FacebookShare on TwitterShare on LinkedInSend email
Follow us on Facebook
2022 AnswerTabsTermsContact us