Re: Query Group by - Should be simple?

You may want to try something along the lines of:

select j1.author, j1.title,

    (select top 1 j2.description
    from junk j2
    where j1.author=j2.author
    and j1.title=j2.title) as description from

    (select author, min(title) as title,     from junk
    group by author) as j1

Hope this helps...

Marius

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.


"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!!!!