Mysql Select columns without group by

1013 views mysql
9

Table Schema

CREATE TABLE `shops` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `shop_code` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL,
  `retailer_name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `shop_closed_temporarily` tinyint(4) NOT NULL DEFAULT '0',
  `verification_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0=no-action,1=verified,2=rejected,3=pending',
  PRIMARY KEY (`id`)
) 

Data in the table

id user_id shop_code retailer_name shop_closed_temporarily verification_status
1 3 XYZ0001 abc 0 3
2 3 XYZ0002 abd 0 3
3 3 XYZ0003 abe 1 2
4 3 XYZ0004 abf 0 1
5 3 XYZ0005 abf 0 3
6 3 XYZ0005 abf 0 3

Problem:

I want to get all the distinct "shop_code" records where verification_status = 3 and shop_closed_temporarily = 0. The result should be having "id" and "shop_code".

Query

SELECT
  s.id,
  s.shop_code
FROM
  shops s
WHERE s.user_id = 3
  AND s.shop_closed_temporarily = 0
  AND s.verification_status = 3
GROUP BY s.shop_code

Want to achieve: 1,2,4,5 OR 1,2,4,6 because row 5 and 6 have similar shop_code.

answered question

Why do you think you need GROUP BY? Why would you choose row 5 or 6? They both meet the criteria. Which would you include or exclude and why? Selecting the ID is going to mean you get them both.

Updated the question with what I want to achieve. If it is possible to get without group by please correct the query

2 Answers

12

You could use an aggregation function eg min()

select min(s.id), s.shop_code 
FROM
  shops s
WHERE s.user_id = 3
  AND s.shop_closed_temporarily = 0
  AND s.verification_status = 3
GROUP BY s.shop_code 

or max()

select max(s.id), s.shop_code 
FROM
  shops s
WHERE s.user_id = 3
  AND s.shop_closed_temporarily = 0
  AND s.verification_status = 3
GROUP BY s.shop_code 

posted this
7

Use DISTINT instead of GROUP BY

SELECT DISTINCT s.id, s.shop_code
FROM shops s
WHERE s.user_id = 3
  AND s.shop_closed_temporarily = 0
  AND s.verification_status = 3

posted this

Have an answer?

JD

Please login first before posting an answer.