CODEWITHSUNDEEP
mysqlsql
user3560827
user3560827

Reputation: 694

SQL - Order by Then Group By

I would like to show items with the cheapest price, then group by item alphabetically.

I have a picture below to best describes what I am looking for:

enter image description here

Upvotes: 1

Views: 659

Answers (4)

sankalpa pathirana
sankalpa pathirana

Reputation: 1

Try This one

SELECT * FROM (table name) GROUP BY item,price,model,color,shop ORDER BY item,price,model,color,shop ASC

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use subquery:

select * 
from table t
where price = (select min(price) from table where Item = t.Item)
order by Item;

However, be careful while same item has two same min price

EDIT:

You can change the where clause and use limit clause 

select * 
from table t
where id = (select id
            from table 
            where Item = t.Item
            order by price desc
            LIMIT 1)
order by Item;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269643

I would use a correlated subquery:

select t.*
from t
where t.price = (select min(t2.price) from t t2 where t2.item = t.item)
order by t.item;

For performance, you want an index on (item, price).

Note: This will return multiple rows for each item, if they all have the same minimum price. If you want to guarantee one row, then you can do:

select t.*
from t
where t.id = (select t2.id
              from t t2
              where t2.item = t.item
              order by t2.price desc
              limit 1
             )
order by t.item;

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

Try joining to a subquery which finds the lowest price record for each item:

SELECT t1.*
FROM yourTable t1
INNER JOIN
(
    SELECT Item, MIN(Price) AS min_price
    FROM yourTable
    GROUP BY Item
) t2
    ON t1.Item = t2.Item AND
       t1.Price = t2.min_price
ORDER BY
    t1.Item;

Upvotes: 0

Related Questions