I'm sure this question should be put in the docs. It's asked at least once a
fortnight. Welcome to the wonderful world of Mysql variables.
create temporary table productorder select *,(@a:= IF(@b=product,@a+1,1)) as
cnt, @b:=product from
products order by product,price;
mysql> select * from productorder;
+----+---------+-------+-------+------+-------------+
| id | product | price | store | cnt | @b:=product |
+----+---------+-------+-------+------+-------------+
| 1 | p1 | 100 | s1 | 1 | p1 |
| 3 | p1 | 110 | s3 | 2 | p1 |
| 2 | p1 | 120 | s2 | 3 | p1 |
| 5 | p2 | 95 | s2 | 1 | p2 |
| 4 | p2 | 120 | s1 | 2 | p2 |
| 6 | p2 | 300 | s3 | 3 | p2 |
| 7 | p3 | 100 | s1 | 1 | p3 |
| 8 | p3 | 120 | s2 | 2 | p3 |
| 9 | p3 | 125 | s3 | 3 | p3 |
| 10 | p3 | 130 | s1 | 4 | p3 |
+----+---------+-------+-------+------+-------------+
10 rows in set (0.04 sec)
select * from productorder where cnt<=1;
mysql> select * from productorder where cnt<=1;
+----+---------+-------+-------+------+-------------+
| id | product | price | store | cnt | @b:=product |
+----+---------+-------+-------+------+-------------+
| 1 | p1 | 100 | s1 | 1 | p1 |
| 5 | p2 | 95 | s2 | 1 | p2 |
| 7 | p3 | 100 | s1 | 1 | p3 |
+----+---------+-------+-------+------+-------------+
3 rows in set (0.00 sec)
Ric.
----- Original Message -----
From: "Ciprian Trofin" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, May 28, 2002 12: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
