CODEWITHSUNDEEP
mysqlsql
user3646742
user3646742

Reputation: 309

select rows based on value of column and distinct column

I want to select row that have a distinct store location based on the number of store, the example shown as below:

id   | item_name   | number_of_store| store_location|
+----+---------+-------------------+-------------+
|  3 | margarine | 2              | QLD         |
|  4 | margarine | 2              | NSW         |
|  5 | wine      | 3              | QLD         |
|  6 | wine      | 3              | NSW         |
|  7 | wine      | 3              | NSW         |
|  8 | laptop    | 1              | QLD         |
+----+---------+-------------------+-------------+

The desired result shown below:

id   | item_name   | number_of_store| store_location|
+----+---------+-------------------+-------------+
|  3 | margarine | 2              | QLD         |
|  4 | margarine | 2              | NSW         |
|  5 | wine      | 3              | QLD         |
|  6 | wine      | 3              | NSW         |
|  8 | laptop    | 1              | QLD         |
+----+---------+-------------------+-------------+

Where I don't care which id column value is returned. What would be the required SQL?

Upvotes: 1

Views: 47

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

You could use GROUP BY:

SELECT id, item_name ,number_of_store, store_location
FROM tab
GROUP BY  item_name ,number_of_store, store_location;
-- will work if ONLY_FULL_GROUP_BY is disabled

Otherwise you need ANY_VALUE or aggregate function like MIN :

SELECT ANY_VALUE(id) AS id, item_name ,number_of_store, store_location
FROM tab
GROUP BY  item_name ,number_of_store, store_location;

DB-Fiddle.com Demo

Upvotes: 2

Related Questions