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 _______________________________________________ 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/[email protected] ** 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.
