select id, product, min(price), store from tablename group by product Better?
Regards Chris ----- Original Message ----- From: "Ciprian Trofin" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, May 28, 2002 1:24 PM Subject: Re[2]: Join or smth. > Doesn't work the way I want to: I want the result to be like: > id | product | price | store > -------------------------------- > 1 | p1 | 100 | s1 > 3 | p1 | 100 | s3 > 5 | p2 | 95 | s2 > 7 | p3 | 100 | s1 > > > CL> select * from table_name where product like 'p1' order by price limit 1; > > >> I have a table that looks smth. like this: > >> > >> id product price store > >> -------------------------- > >> 1 p1 100 s1 > >> 2 p1 120 s2 > >> 3 p1 100 s3 > >> 4 p2 120 s1 > >> 5 p2 95 s2 > >> 6 p2 300 s3 > >> 7 p3 100 s1 > >> 8 p3 120 s2 > >> 9 p3 125 s3 > >> 10 p3 130 s1 > >> > >> CREATE TABLE `products` ( > >> `id` tinyint(3) unsigned NOT NULL auto_increment, > >> `product` varchar(4) NOT NULL default '0', > >> `price` smallint(3) unsigned NOT NULL default '0', > >> `store` varchar(4) NOT NULL default '0', > >> PRIMARY KEY (`id`) > >> ) TYPE=MyISAM; > >> > >> # > >> # Dumping data for table 'products' > >> # > >> > >> INSERT INTO products VALUES("1","p1","100","s1"); > >> INSERT INTO products VALUES("2","p1","120","s2"); > >> INSERT INTO products VALUES("3","p1","110","s3"); > >> INSERT INTO products VALUES("4","p2","120","s1"); > >> INSERT INTO products VALUES("5","p2","95","s2"); > >> INSERT INTO products VALUES("6","p2","300","s3"); > >> INSERT INTO products VALUES("7","p3","100","s1"); > >> INSERT INTO products VALUES("8","p3","120","s2"); > >> INSERT INTO products VALUES("9","p3","125","s3"); > >> INSERT INTO products VALUES("10","p3","130","s1"); > >> > >> > >> I want to build a query to find out where I can find the least expensive > >> product. > >> > >> I know how to solve the problem using a script language (PHP) but I'd like > >> to do it SQL-style :). I guess it can be done using some sort of JOIN. > >> > > -- > Ciprian > > > Un cuvant de sfarsit: > > Useless fact: Odds of being killed in a car crash are 1 in 5000. > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php