CODEWITHSUNDEEP
mysqlsqlsql-order-by
sandeep thoomula
sandeep thoomula

Reputation: 23

How to get min and max values and corresponding ids in a single row

I have a table called product with 3 fields:

prod_id  price  pname
1        100    abc
2        200    bbc
3        300    cbc

I want to get data of min and max in one row, i.e.:

 minprice  prod_id1   maxprice   prod_id2
 100       1          300        3

I tried with the following query but not able to find the exact result(if i have more data)

SELECT s1.prod_id as prod_id1, 
       s2.prod_id as prod_id2, 
       min(s1.price) as minprice,
       max(s2.price) as maxprice 
FROM product s1
INNER JOIN product s2 ON s1.prod_id = s2.prod_id

Upvotes: 1

Views: 1255

Answers (3)

juergen d
juergen d

Reputation: 204924

SELECT s1.prod_id as prod_id1, 
       s2.prod_id as prod_id2, 
       s1.price as maxprice,
       s2.price as minprice 
FROM products s1
INNER JOIN products s2 ON s1.price > s2.price
order by s1.price desc, s2.price asc
limit 1

Demo

Upvotes: 1

Salman Arshad
Salman Arshad

Reputation: 272446

You can do this with LIMIT:

SELECT
    pmin.price AS minprice, 
    pmin.prod_id AS minprice_prodid,
    pmax.price AS maxprice,
    pmax.prod_id AS maxprice_prodid
FROM (
    SELECT prod_id, price FROM product ORDER BY price LIMIT 1
) pmin
CROSS JOIN (
    SELECT prod_id, price FROM product ORDER BY price DESC LIMIT 1
) pmax

Upvotes: 2

Fahmi
Fahmi

Reputation: 37493

You can try below -

select X.id as prod_id1, minprice, Y.id as prod_id2, maxprice
from
(
select id, price as minprice from tablename
where price =(select min(price) from tablename b)
)X cross join
(
select id, price as maxprice from tablename
where price =(select max(price) from tablename c)
)Y

Upvotes: 1

Related Questions