Query Group by - Should be simple?

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 Fri Mar 22 2002 - 21:56:00 CST