Menu
I can't seem to find a suitable solution for the following (probably an age old) problem so hoping someone can shed some light. I need to return 1 distinct column along with other non distinct columns in mySQL.
I have the following table in mySQL:
id name destination rating country ---------------------------------------------------- 1 James Barbados 5 WI 2 Andrew Antigua 6 WI 3 James Barbados 3 WI 4 Declan Trinidad 2 WI 5 Steve Barbados 4 WI 6 Declan Trinidad 3 WI I would like SQL statement to return the DISTINCT name along with the destination, rating based on country.
id name destination rating country ---------------------------------------------------- 1 James Barbados 5 WI 2 Andrew Antigua 6 WI 4 Declan Trinidad 2 WI 5 Steve Barbados 4 WI As you can see, James and Declan have different ratings, but the same name, so they are returned only once.
The following query returns all rows because the ratings are different. Is there anyway I can return the above result set?
SELECT (distinct name), destination, rating FROM table WHERE country = 'WI' ORDER BY id 
274
Answer. Yes, the DISTINCT clause can be applied to any valid SELECT query. It is important to note that DISTINCT will filter out all rows that are not unique in terms of all selected columns. Feel free to test this out in the editor to see what happens!
To get the identical rows (on four columns agent_code, ord_amount, cust_code, and ord_num) once from the orders table , the following SQL statement can be used : SQL Code: SELECT DISTINCT agent_code,ord_amount,cust_code,ord_num FROM orders WHERE agent_code='A002';
Yes, DISTINCT works on all combinations of column values for all columns in the SELECT clause.
Adding the DISTINCT keyword to a SELECT query causes it to return only unique values for the specified column list so that duplicate rows are removed from the result set. Since DISTINCT operates on all of the fields in SELECT's column list, it can't be applied to an individual field that are part of a larger group.
Using a subquery, you can get the highest id for each name, then select the rest of the rows based on that:
SELECT * FROM table WHERE id IN ( SELECT MAX(id) FROM table GROUP BY name ) If you'd prefer, use MIN(id) to get the first record for each name instead of the last.
It can also be done with an INNER JOIN against the subquery. For this purpose the performance should be similar, and sometimes you need to join on two columns from the subquery.
SELECT table.* FROM table INNER JOIN ( SELECT MAX(id) AS id FROM table GROUP BY name ) maxid ON table.id = maxid.id If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
