Re: Query Group by - Should be simple?

Here is another one you can try

select * from
(select author,min(title) over (partition by author) as min_title,description,title
from table)
where title=min_title

"Susan Goodhart" <susan_at_goodhart.com> wrote in message 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 Sun Mar 24 2002 - 10:30:53 CST