Re: Query Group by - Should be simple?

split your query into two...

in the first one you use the min() function for every author... let's say you'll name it query1.
the second one will use query1 joined with books table, so that you can properly find the description.

Loz
"Susan Goodhart" <susan_at_goodhart.com> ha scritto nel messaggio news:bfd1e48d.0203221956.56a1cc44_at_posting.google.com...
> I have what I thought was a fairly simple situation but I must be
> totally missing the boat since I can't get the output I want (the
> actual problem is much more complex across multiple tables - I have
> mocked up the following to get down to the minimum for a solution)
>
> Table struction looks like:
> AUTHOR TITLE DESCRIPTION
> ----------------------------
> JOHN Book A xxx
> JOHN Book B ccc
> SUSAN Book C yyy
> SUSAN Book D ddd
>
> I want to group by the author, get the minimum title, and then get the
> CORRESPONDING description to the minimum title. Getting the
> corresponding DESCRIPTION is the problem. Want:
>
>
> AUTHOR TITLE DESCRIPTION
> ----------------------------
> JOHN Book A xxx
> SUSAN Book C yyy
>
> First pass at query doesn't work since I get all the records - what I
> really want to do is leave the description out of the group by clause,
> but that is not allowable syntax. I have to either put an aggregate
> function on the DESCRIPTION field or put it in the group by clause,
> neither of which work!
>
> This does not work:
> select author, min(title), description
> from junk
> group by author, description
>
> And this does not work:
> select author, min(title), min(description)
> from junk
> group by author
>
> And this does not work:
> select author, min(title), description
> from junk
> group by author, description
> having min(title) = title
>
> Any help would be greatly appreciated!!!!
Received on Sat Mar 23 2002 - 07:10:48 CST