From: Michael Widenius
Date: March 12 1999 12:29am
Subject: mysql: Need help building query - reposted
List-Archive: http://lists.mysql.com/mysql/70
Message-Id: <14056.24130.881316.570809@monty.pp.sci.fi>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

>>>>> "Matthias" == Matthias Pigulla <mp@stripped> writes:

Matthias> Hi Monty & folks,
Matthias> some weeks ago I posted the following (snipped). Sorry for bothering you
Matthias> again.

Matthias> Matthias Pigulla wrote:
>> I have tables for the threads (id and topic), postings (id, key
>> referencing a thread, key referencing its author and the text) and the
>> users (id, properties i.e. nickname).
>> Now, I want to generate a list of all discussion threads in a certain
>> board. I want it to contain *) the date of the last posting *) the user
>> who initiated the thread with the first posting.

Matthias> The problem was how to select a certain row of a set before the rows are
Matthias> GROUPed together. { All rows have the same value in one column, but
Matthias> different values in another. I want to get the row - and only one row
Matthias> for each value in the first column - with the smallest value in the
Matthias> second column. }

The following entry from the Manual should help you:

In some specific cases, you can use MIN() and MAX()
get a specific column even if it isn't unique. The following gives the value
of 'column' from the row with the smallest 'sort' value:

substr(MIN(concat(sort,space(6-length(sort)),column),7,length(column)))


Matthias> I was pointed to the HAVING clause, but can't get it to work. Neither
Matthias> can I find a good explanation for it in the docs. Can you explain it to
Matthias> me please?

HAVING can't help you;  Its exactly like a WHERE, but on the result set.

Matthias> What about SELECT DISTINCT? Does it work in the .19 release?

It works in all MySQL relesaes, but will not probably not solve your problem.

Regards,
Monty