* Petre Agenbag > I am having trouble with a query to return only one instance of a name > in a table.
SELECT DISTINCT name FROM table > For instance: > > id name town date > 1 jack town1 2002-12-12 > 2 john town2 2002-12-13 > 3 jack town4 2002-12-21 > 4 jack town5 2002-12-30 > 5 john town2 2002-12-15 > > I want to have a query that will return this: > > id name town date > 4 jack town5 2002-12-30 > 5 john town2 2002-12-15 Ok, you don't only want the name, you want the corresponding row too...? Creating some test data: use test; create table pa (id int,name varchar(10), town varchar(10),date datetime); insert pa values (1,'jack','town1','2002-12-12'); insert pa values (2,'john','town2','2002-12-13'); insert pa values (3,'jack','town4','2002-12-21'); insert pa values (4,'jack','town5','2002-12-30'); insert pa values (5,'john','town2','2002-12-15'); > So, I want only the last entry based on the name. Last entry _for_each_name_ based on the highest id? Or based on the latest date? > I tried > select MAX(id), name from table group by name mysql> select MAX(id), name from pa group by name; +---------+------+ | MAX(id) | name | +---------+------+ | 4 | jack | | 5 | john | +---------+------+ 2 rows in set (0.00 sec) No problem here... > select distinct name from table group by name mysql> select distinct name from pa group by name; +------+ | name | +------+ | jack | | john | +------+ 2 rows in set (0.00 sec) Seems right. > > But both sometimes fail. hm... sometimes...? How do they fail? ...none of them would return the entire row...I thought you wanted the entire row? Try this: mysql> SELECT name,MAX(CONCAT(id,'|',town,'|',date)) FROM pa GROUP BY name; +------+-----------------------------------+ | name | MAX(CONCAT(id,'|',town,'|',date)) | +------+-----------------------------------+ | jack | 4|town5|2002-12-30 00:00:00 | | john | 5|town2|2002-12-15 00:00:00 | +------+-----------------------------------+ 2 rows in set (0.00 sec) (This is called the MAX-CONCAT trick, see <URL: http://www.mysql.com/doc/en/example-Maximum-column-group-row.html >) The MAX() group function is used (in this case) to find the highest id for each name, and CONCAT() is used to collect data from the other fields of the row. The reason for doing this is that you can not use columns in a normal way in a GROUP BY query, only the columns you GROUP BY, and columns used in group functions: mysql> select max(id),name,town,date from pa GROUP by name; # THIS IS WRONG! +---------+------+-------+---------------------+ | max(id) | name | town | date | +---------+------+-------+---------------------+ | 4 | jack | town1 | 2002-12-12 00:00:00 | | 5 | john | town2 | 2002-12-13 00:00:00 | +---------+------+-------+---------------------+ 2 rows in set (0.01 sec) This is the wrong result, the row with id=4 should have town='town5', and the date is wrong too. This is because 'town' and 'date' are not used in the GROUP BY clause, and group functions are not used for these columns. This is not a bug, it is an implicit limitation in the SQL language. If you really meant the last date, not the highest id, it would be like this: SELECT name,MAX(CONCAT(date,'|',town,'|',id)) FROM pa GROUP BY name; Because 'date' now is the first part of the value that goes into MAX(), the highest date will be found. This is not magic, it is because the mysql date format (yyyy-mm-dd) will sort correctly in a string context. Dates stored in a "dd-mm-yyyy" format would not work in this case: 31th january would be 'later than' 30th december. Splitting the output column on the "|" character should be easy using any scripting language. > Note: I am using a sample table here, I suspect that in my table with > the actual data, that there might be spaces before or after the names, > so I might need to clean them up from the table ( any quick way of doing > this?) UPDATE table SET name=TRIM(name) > as it seems to be the only logical explanation for the queries to > return multiple instances. Also, the rows are not 100% unique wrt the > other fields, so that might be why distinct won't work? > > Any ideas? If you still have problems, show us some examples of your queries & results. HTH, -- Roger --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
