CODEWITHSUNDEEP
sqlsqlite
nub programmer
nub programmer

Reputation: 29

How to choose minimum value row in Group By sql

Here's a closely resembling sqlite database that I'm working with -

CREATE TABLE sampleData(
   pincode INTEGER NOT NULL,
   place_id INTEGER NOT NULL,
   price INTEGER NOT NULL,
   name TEXT NOT NULL,
   UNIQUE(pincode, place_id)
);

I was looking to find names of places in every pincode(only 1 place per pincode) which has the lowest price there. The simplest query I could come up with -

SELECT * FROM sampleData 
WHERE price > 0
GROUP BY pincode
ORDER BY PRICE

which seems to work in sqlite(?) but fails to work in pg9.6 and gives wrong result in Mysql. Here's another one which seems to give same result -

SELECT * FROM sampleData 
WHERE price > 0
GROUP BY pincode
HAVING MIN(PRICE)

which has similar results as last one. I was wondering what's wrong with these queries and would love some help crafting the correct one.

SQLFIDDLE

Upvotes: 2

Views: 118

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Your first query is malformed SQL. SELECT * doesn't make sense with GROUP BY. It so happens that SQLite extends the SQL language to do what you want. I consider that a bad thing.

In any case, you can do:

SELECT sd.*
FROM sampleData sd
WHERE sd.price = (SELECT MIN(sd2.price)
                  FROM sampleData sd2
                  WHERE sd2.price > 0 AND
                        sd2.pincode = sd.pincode
                 );

This can return duplicate rows, if multiple places have the same price. Assuming place_id is unique, you can revise this to:

SELECT sd.*
FROM sampleData sd
WHERE sd.place_id = (SELECT sd2.place_id
                     FROM sampleData sd2
                     WHERE sd2.price > 0 AND
                           sd2.pincode = sd.pincode
                     ORDER BY sd2.price ASC
                     LIMIT 1
                    );

Upvotes: 4

mkRabbani
mkRabbani

Reputation: 16908

Try This...

SELECT pincode,place,name,MIN(PRICE)
FROM sampleData 
WHERE price > 0
GROUP BY pincode,place,name

Upvotes: 0

forpas
forpas

Reputation: 164099

You need a query to get the min price for each pincode and then join it to the table:

select s.* 
from sampledata s inner join (
  select pincode, min(price) price
  from sampledata
  group by pincode
) g on g.pincode = s.pincode and g.price = s.price

See the demo.
Results:

| pincode | place_id | price | name   |
| ------- | -------- | ----- | ------ |
| 123     | 2        | 13    | Place2 |
| 222     | 18       | 21    | Place5 |
| 456     | 5        | 200   | Place3 |

Upvotes: 0

Related Questions