* Petre Agenbag
> 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?
Not exactly, but similarly. You should not use 'distinct' in this case.
GROUP BY is used to create only one row for each name, 'distinct' is not
needed.
> 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.
No, that won't happen.
> 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.
This is nothing to worry about, because the CONCAT() function makes
everything a string, and you _can_ MAX() a string. The CONCAT() will make a
string out of every id/anything_else combination, and MAX() is used on the
created string value to find the highest combination for each name. However,
"2|anything_else" is bigger than "11|anything_else", so you may need to
prefix 'id' with zeroes. (See the use of LPAD() in the example in the
manual.)
> However, I'm already discrediting/doubting a solution without even testing
> it... Geez, I'm a prick hey? ;)
Skepticism is fine, but I think you should take a closer look at the example
in the manual. :)
> 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.
Problems like this can sometimes be solved with a self join, but be aware
when you have big amounts of data, this could be very heavy for the server
if there are many rows with the same name. This works on your test data:
select t1.id, t1.name, t1.something
from this_table t1
left join this_table t2 on
t2.id>t1.id and t2.name=t1.name
where
t1.`else` = 'test2' and
t2.id IS NULL;
The idea here is to find any row (t2) with the same name as the current one
(t1), but with a higher id. If we find it, we _ignore_ the row in the result
set, because this is not the highest id. Or, to put it the other way around,
we only want the rows where we did not find any t2 (t2.id IS NULL).
> 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.
Both/any of the methods mentioned above could be usefull for you, I think.
[...]
> Thanks in any event for your time and interest.
happy to help, :)
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
