how to pass min function in where clause in mysql

Question

0.00/5 (No votes)

See more:

i have the follwing tables.

SQL

supplier_info table:
+-----+----------------------------------+
| sid | sname                            |
+-----+----------------------------------+
|   1 | Shree Mangireesh Printing Press  |
|   2 | SAI INDUSTRIES                   |
|   3 | GLOBE GLASS CONTAINERS           |
|   4 | SONIC BIOCHEM EXTRACTIONS LTD    |
|   5 | SPACE AGE PLASTIC INDUSTRIES     |
+-----+----------------------------------+

SQL

rm_price table:

+---------+------------+-----------+----------+
| rmname  | inv_date   | unitprice | quantity |
+---------+------------+-----------+----------+
| Aerosil | 2013-02-12 | 4250      | 1        |
| Aerosil | 2013-03-03 | 4050      | 1        |
| Aerosil | 2013-03-29 | 5050      | 1        |
| Aerosil | 2013-04-20 | 5950      | 1        |
| Aerosil | 2013-05-03 | 5150      | 1        |
| Aerosil | 2013-05-13 | 4250      | 1        |
| Aerosil | 2013-05-25 | 4050      | 1        |
| Aerosil | 2013-06-01 | 3550      | 1        |
| Aerosil | 2013-06-30 | 5055      | 1        |
| Aerosil | 2013-07-15 | 5250      | 1        |
| Aerosil | 2013-08-03 | 5500      | 1        |
| Aerosil | 2013-09-12 | 5000      | 1        |
| Aerosil | 2013-10-15 | 5350      | 1        |
| Aerosil | 2013-11-18 | 5900      | 1        |
| Aerosil | 2013-12-02 | 5200      | 1        |
+---------+------------+-----------+----------+
<pre>

i need result like  sname,min(unitprice)and respective inv_date,max(unitprice) and respective inv_date,quantity,current unitprice.

result is :
<pre lang="SQL">
+---------------------+----------------+----------+----------------+----------+
| rmname              | min(unitprice) | min_date | max(unitprice) | max_date |
+---------------------+----------------+----------+----------------+----------+
| Aerosil             | 3550           |2013-06-01| 5950           |2013-04-20| 
+---------------------+----------------+----------+----------------+----------+

i have tried the follwing query but it returns zero for inv_date

SQL

mysql> select rmname,min(unitprice),inv_date in(select inv_date from rm_price wh
ere unitprice=(select min(unitprice) from rm_price)) as min_date,max(unitprice),
inv_date in(select inv_date from rm_price where unitprice=(select max(unitprice)
 from rm_price))as max_date from rm_price group by rmname;

My query result is:

SQL

+---------------------+----------------+----------+----------------+----------+
| rmname              | min(unitprice) | min_date | max(unitprice) | max_date |
+---------------------+----------------+----------+----------------+----------+
| Aerosil             | 3550           |        0 | 5950           |        0 |
+---------------------+----------------+----------+----------------+----------+

It takes more time to execute the query(cause place multiple substrings). is there any way to get the records for the above requirement. please help me out.

Thanks in Advance.

Posted 19-Dec-13 18:55pm

User-10031173

Updated 19-Dec-13 19:02pm

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2013-12-19T20:18:00

Solution 2

If you want to search database based on result of aggregate function, you should use HAVING clause[^]!

But in your case i would suggest to use variables[^]:

SQL

SELECT @minprice = MIN(unitprice) FROM rm_price;
SELECT @maxprice = MAX(unitprice) FROM rm_price;

SELECT rmname, @minprice as MinPrice, (SELECT inv_date FROM rm_price WHERE unitprice = @minprice AND rmname = t.rmname) AS MinDate,
    @maxprice AS MaxPrice, (SELECT inv_date FROM rm_price WHERE unitprice = @minprice AND rmname = t.rmname) AS MaxDate
FROM rm_price AS t;

It should execute quite fast.

Posted 19-Dec-13 20:18pm

Maciej Los

Updated 20-Dec-13 0:42am

v2

Comments

User-10031173 20-Dec-13 5:33am

but its giving error like:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'SELEC
T @maxprice = MAX(unitprice) FROM rm_price
SELECT rmname, @minprice as MinP' at line 2

Maciej Los 20-Dec-13 6:43am

Sorry, i forgot to add ; at the end of each line ;)

[no name] 20-Dec-13 10:17am

Why a lawyer knows this sql stuff. Simply great!

Maciej Los 21-Dec-13 15:38pm

Because i'm SQL lawyer ;)

[no name] 21-Dec-13 15:40pm

*lol* ok. But unfortunately more then 5 is not possible ;)

Maciej Los 21-Dec-13 16:36pm

Thank you very much!

Have a Happy Christmas and - as we say in Poland - rich Santa Claus!

BK 4 code · Answer 2 · 2013-12-19T19:32:00

Solution 1

HI You should try simple query like following

SQL

select T.rmname,R.unitprice as Min,R.inv_date,M.unitprice as Max,M.inv_date from rm_price R inner join 
(select rmname,min(unitprice) Min,max(unitprice) Max from rm_Price )T
on R.unitprice=T.Min
inner join rm_Price M on M.unitprice=T.Max

Posted 19-Dec-13 19:32pm

BK 4 code

Comments

User-10031173 20-Dec-13 5:16am

its correct.
also i need current price and respective date,sname and group by suppliername for above query. where can i place these columns. i tried many ways but giving wrong results.

User-10031173 20-Dec-13 5:21am

I tried the below query. giving correct result, but i need current unitprice and respective inv_date,sname group by sname. how can i place these in the follwing query. please help me

select r.sid,r.rmname,r.inv_date as dateofmax,
r.unitprice as maxprice,
r3.inv_date as minofdate,r3.unitprice as minprice,
r.unitprice-r3.unitprice as pricediff
from rm_price r
left join rm_price r2 on r.rmname=r2.rmname and r.unitprice<r2.unitprice
left="" join="" rm_price="" r3
="" on="" r.rmname="r3.rmname" and="" r3.unitprice<="r.unitprice
" r4=""
="" r3.unitprice="">r4.unitprice
where r2.unitprice is null
and r4.unitprice is null group by r.rmname \G

BK 4 code 20-Dec-13 5:24am

But i could not find any reference key between these two tables.

User-10031173 20-Dec-13 7:44am

sid is the reference key

User-10031173 20-Dec-13 7:45am

in rm_price table sid is also there i forgot to mention sid