Thanks Roger, I will give it a bash. PS, just to get my head around things.
The distinct clause will return only one occurrance of the implicated field. distinctrow would do the same, but for an entire row. what I am looking for is something like distinctset(field1,field2,field7) , is this what the MAX-CONCAT "trick" attempts to do? You see, what I'm getting at is that I am afraid that even with the MAX-CONCAT trick it will not "know" to include the "anything_else" from the distinct row with the highest id, but rather the first occurrance thereof. And by the way, as *i think* I mentioned, "anything_else" would most definately NOT be an integer or anything that I would think can be "MAX"'ed. However, I'm already discrediting/doubting a solution without even testing it... Geez, I'm a prick hey? ;) Maybe you can also help me to think about the following: id name something else 1 joe test test2 2 joe test test3 3 jack testa test 4 jack testb test1 5 jack testb test2 Now I want to query with the following pseudo string: I first need to get hold of the distinct set of name,something with the highest row id, and basically remove everything else from the equation. For instance, should I do select id,name, something from this_table where else = 'test2' it should NOT return a value. I know this doesn't make sense, because this query is valid, and *should* by rights return 1 joe test 5 jack testb BUT, that is my dilemma, for what I'm lookinmg for, only 5 jack testb should return because that row did in fact match the criteria for the "else = test2", BUT, it also was indeed the "last" row with the distinct set of jack and testb. My problem is having that last condition added to the string that says .... where else = 'test2' BUT only if distinct(name, something) is the row with the highest id. When I think of this, it seems that the only way to do this is to first create a temporary table that only contains the "last" row for that particular name/something combination, and only once you have that table, to query it again with a "where else ='test2'", cause then you KNOW, there can only be 1 or 0 rows in the result set. However, to create this temporary table that INCLUDES the else, I am back at square one. It's easy enough to get a temp table with only one occurrance of say "name" OR "something", but to get one with a COMBINATION of "name" AND "something" is my achiles heal... Again, sorry if your proposed solution does exactly this ( touch wood, and a BIG HUGE thank you then), I just want to make sure that you (or anyone else interested in this thread) understands exactly what the question is ( believe me, after a while thinking of it myself, I sometimes lose the entire question completely inbetween all the "if" "and" and "or"'s flying through my head.). Thanks in any event for your time and interest. -----Original Message----- From: Roger Baklund [mailto:[EMAIL PROTECTED] Sent: Friday, August 01, 2003 2:49 AM To: [EMAIL PROTECTED] Cc: Petre Agenbag Subject: Re: help to structure query without using subselects * Petre Agenbag [...] > This works: > > select distinct name, max(id) as mid from table group by name > > but it only returns the name and the highest id for such a name. > > if I try this: > > select distinct name, max(id) as mid, anything_else from table group by > name > > it returns the FIRST "anything_else", and not the value in the last row > for that name ( as I would like it to) Try the MAX-CONCAT trick: select distinct name, max(concat(id,'|',anything_else,'|',even_more)) as name_info from table group by name Then you must programatically split the 'name_info' column back to the individual fields, or use SUBSTRING, like in the example in the manual: <URL: http://www.mysql.com/doc/en/example-Maximum-column-group-row.html > HTH, -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
