Tony Montana Tony Montana - 2 months ago 6
MySQL Question

Not getting result using GROUP BY, ORDER BY and HAVING

I am trying to fetch records combining

GROUP BY
,
ORDER BY
and
HAVING
, but I am not getting result / desirable result. This is my table
test
:

+--------------------------------------+-----+---------------------+------+
| id | no | date_entered | name |
+--------------------------------------+-----+----------------------------+
| c44d1977-0193-a2f1-00f9-57e4c53ba416 | 104 | 2016-09-23 06:02:16 | Z-44 |
| ca1879a2-df08-4fe3-b144-57e4d2ab0c62 | 104 | 2016-09-23 06:57:35 | Z-44 |
| 5dd46d35-358a-314e-30fa-57e4d2ca92ad | 104 | 2016-09-23 06:59:17 | Z-44 |
| d474cc6a-e7a1-15d1-d209-57e4c5aa607d | 105 | 2016-09-23 05:03:08 | Z-45 |
| db77b687-763d-b63a-be77-57e4d2e246fb | 105 | 2016-09-23 05:51:41 | Z-45 |
| 70f0f514-c0fd-ddfd-55a6-57e4d21b5e3c | 106 | 2016-09-23 05:29:39 | Z-46 |
+--------------------------------------+-----+---------------------+------+


And I want result something like this :

+--------------------------------------+-----+----------+
| id | no | COUNT(*) |
+--------------------------------------+-----+----------+
| 5dd46d35-358a-314e-30fa-57e4d2ca92ad | 104 | 3 |
| db77b687-763d-b63a-be77-57e4d2e246fb | 105 | 3 |
+--------------------------------------+-----+----------+


Here I want to fetch only those records which are belongs to same
no
and are oldest one. Also this query should run where there are more than one records belongs to same
no
. So this is the query I am using

SELECT id, no, COUNT(*)
FROM test
GROUP BY no
HAVING COUNT(*) > 1
ORDER BY date_entered DESC;


But I am getting this result

+--------------------------------------+-----+----------+
| id | no | COUNT(*) |
+--------------------------------------+-----+----------+
| c44d1977-0193-a2f1-00f9-57e4c53ba416 | 104 | 3 |
| d474cc6a-e7a1-15d1-d209-57e4c5aa607d | 105 | 3 |
+--------------------------------------+-----+----------+


Now, I have look around for help and I got this How to combine GROUP BY, ORDER BY and HAVING and as mentioned in accepted answer that
However, you need to pick the fields you ACTUALLY WANT then select only those and group by them
, so I tried this query also:

SELECT id, no, count(*)
FROM test
GROUP BY id, no
HAVING count(*) > 1
ORDER BY date_entered DESC;


But after executing this query, I got none. Kindly guide me here.

Answer

One method is to use the substring_index()/group_concat() trick:

SELECT SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY date_entered DESC), ',', 1) as id,
       no, COUNT(*) 
FROM test 
GROUP BY no 
HAVING COUNT(*) > 1 ;

There are some limitations on this approach. The default internal size for GROUP_CONCAT() is 1,024 bytes. This is easily reconfigured, but it assumes that you do not have too many ids for a given number.

There are alternative approaches, using variables or more complex SQL statements. However, this is the simplest for fitting into your current query.

Comments