select j1.author, j1.mintitle, j2.description
from (select author, min(title) mintitle from junk group by author) j1,
junk j2
where j2.author = j1.author and j2.title = j1.mintitle;
--
Ken Denny
http://www.kendenny.com/
My Mom said she learned how to swim when someone took her out in the lake
and threw her off the boat. I said, "Mom, they weren't trying to teach you
how to swim." - Paula Poundstone
susan_at_goodhart.com (Susan Goodhart) wrote in
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 - 10:57:50 CST
