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/CACF5tUnW_sTZ5uENt+osTwTU6X5gf0VssPqf=jngbtoitds...@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.
