CODEWITHSUNDEEP
mysqlsql
Dino
Dino

Reputation: 1457

Get number of rows from mysql where condition is met and the max value in 1 query

I have the following table - order:

enter image description here

Currently using the following query:

select `id` 
from `order` 
where `userid_FK`=24242 and `productid_FK`=5

which returns - 32, 33, 34

How can I also select max(price) 197.0000 in one query is this possible (the max price for that user and productid)?

Thanks

Upvotes: 0

Views: 35

Answers (2)

lalithkumar
lalithkumar

Reputation: 3540

you need to use MAX .

This will give you max price with one row.

select `id`,max(price) 
from `order` 
where `userid_FK`=24242 and `productid_FK`=5

If you want with all records:

select `id`,(select max(price) 
from `order` 
where `userid_FK`=24242 and `productid_FK`=5) ) as maxrate
from `order` 
where `userid_FK`=24242 and `productid_FK`=5

Upvotes: 1

Oto Shavadze
Oto Shavadze

Reputation: 42783

One way is using subquery in select list

select `id`, (select max(price) from `order` where `userid_FK`=24242 and `productid_FK`=5) as maxprice
from `order` 
where `userid_FK`=24242 and `productid_FK`=5

Upvotes: 1

Related Questions