Hi Malcom,
I am glad to hear you again. Hope you are well.
I think that you need something like this:
SELECT * FROM [products] p
inner join (SELECT prod_type, min(prod_rank) prod_rank FROM [products] p2
GROUP BY prod_type
) xx
ON p.prod_type= xx.prod_type and p.prod_rank = xx.prod_rank
ORDER BY p.prod_rank
Hope this helps
Vassilis Aggelakos
On Thu, Sep 1, 2011 at 1:48 PM, Malcolm Greene <[email protected]> wrote:
> I have a table like the following:
>
> CREATE TABLE products ( prod_type char(10), prod_rank int, prod_vend
> char(10), prod_note char(10) )
> INSERT INTO products ( prod_type, prod_rank, prod_vend, prod_note )
> VALUES ( 'soap', 1, 'jane', 'jane soap' )
> INSERT INTO products ( prod_type, prod_rank, prod_vend, prod_note )
> VALUES ( 'soap', 2, 'mark', 'mark soap' )
> INSERT INTO products ( prod_type, prod_rank, prod_vend, prod_note )
> VALUES ( 'soap', 5, 'jack', 'jack soap' )
> INSERT INTO products ( prod_type, prod_rank, prod_vend, prod_note )
> VALUES ( 'milk', 4, 'fred', 'fred milk' )
> INSERT INTO products ( prod_type, prod_rank, prod_vend, prod_note )
> VALUES ( 'milk', 5, 'kath', 'kath milk' )
> INSERT INTO products ( prod_type, prod_rank, prod_vend, prod_note )
> VALUES ( 'buns', 1, 'mark', 'mark buns' )
> INSERT INTO products ( prod_type, prod_rank, prod_vend, prod_note )
> VALUES ( 'buns', 3, 'jane', 'jane buns' )
> INSERT INTO products ( prod_type, prod_rank, prod_vend, prod_note )
> VALUES ( 'buns', 5, 'fred', 'fred buns' )
> INSERT INTO products ( prod_type, prod_rank, prod_vend, prod_note )
> VALUES ( 'dogs', 7, 'fred', 'fred dogs' )
> INSERT INTO products ( prod_type, prod_rank, prod_vend, prod_note )
> VALUES ( 'cats', 8, 'bill', 'bill cats' )
>
> I'm looking for a SQL query that will group this table by prod_type and
> return the records associated with the min value of prod_rank.
>
> In other words, using the above table, here's the result I'm looking
> for.
>
> soap | 1 | jane ...
> milk | 4 | fred ...
> buns | 1 | mark ...
> dogs | 7 | fred ...
> cats | 8 | bill ...
>
> Is this type of query possible via pure sQL? I know I can write code
> that will order the table by prod_type and prod_rank (desc) and take
> only the first record for each prod_type, but I'm looking for a SQL
> query I can use across database engines.
>
> Thanks!
> Malcolm
>
[excessive quoting removed by server]
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/CACF5tUmy1czE=_0omn_phbvo8oyrmbyq5714s4ev4iuyjwt...@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
