How to select a single row from a sql table with multiple rows having the same value in a column

advertisements

I have an sql table which current looks like this:

id |   Name   | Age|   City
______________________________
1  |   Mike   | 26 | New York
2  |   Nick   | 32 | London
3  |   Anne   | 22 | New York
4  |   Andy   | 24 | Lagos
5  |   Nickie | 27 | Paris
6  |   Sam    | 26 | London

I need to query the table to return something like:

id |   Name   | Age|   City
______________________________
1  |   Mike   | 26 | New York
2  |   Nick   | 32 | London
3  |   Andy   | 24 | Lagos
4  |   Nickie | 27 | Paris

so that all rows with same value for a particular column (city in this example) gets returned just once (only 1 of multiple rows with same column values gets returned). If i add a timestamp column, then i want only the latest row displayed. How do i write this sql


you can write required query like below if you have a column date_edited with type TIMESTAMP .

SET @@sql_mode='ONLY_FULL_GROUP_BY';
SELECT Name, Age,
GROUP_CONCAT(DISTINCT City ORDER BY date_edited DESC) AS city
FROM TABLE_NAME
GROUP BY City
ORDER BY date_edited DESC