In database my query getting first row When i grouped

Admin 1
Viewed 58
SELECT
    EVENT_ID, COUNT(*), SEQUENCE_NBR 
FROM
    ALERTS  
WHERE 
    ACKNOWLEDGED = 0 
    AND SRC_EXT = '7878'  
GROUP BY
    EVENT_ID 
ORDER BY 
    COUNT(*) DESC;

Running this query to get id,count and sequence number. by group by event id and count i am getting sequence number first row .

+----------+--------------+
| EVENT_ID | SEQUENCE_NBR |
+----------+--------------+
|      150 |         9752 |
|      150 |         9764 |
|      150 |         9775 |
|      170 |         9755 |
|      170 |         9763 |
|      170 |         9774 |
|      217 |         9748 |
|      217 |         9759 |
|      217 |         9770 |
|      218 |         9751 |
|      218 |         9762 |
|      218 |         9773 |
|      273 |         9749 |
|      273 |         9760 |
|      273 |         9771 |
|      285 |         9750 |
|      285 |         9761 |
|      285 |         9772 |
+----------+--------------+

This is my data in db by using above query

+----------+----------+--------------+
| EVENT_ID | COUNT(*) | SEQUENCE_NBR |
+----------+----------+--------------+
|      150 |        3 |         9752 |
|      170 |        3 |         9755 |
|      217 |        3 |         9748 |
|      218 |        3 |         9751 |
|      273 |        3 |         9749 |
|      285 |        3 |         9750 |
+----------+----------+--------------+

i need data in same format with seuence number should

150 |        3 |         9775
sql
2 Answers

you can try like below

    SELECT
    EVENT_ID, COUNT(*), SEQUENCE_NBR 
FROM
    ALERTS  
WHERE 
    ACKNOWLEDGED = 0 
    AND SRC_EXT = '7878'  
GROUP BY
    EVENT_ID 
ORDER BY 
    COUNT(*) DESC,SEQUENCE_NBR desc;
user_68085830

Your query is malformed. You have SEQUENCE_NBR in the SELECT, but it is not in the GROUP BY. In most databases (including the more recent versions of MySQL), this generates an error. Happily that is so.

If you want the maximum SEQUENCE_NBR, then use the MAX() function:

SELECT EVENT_ID, COUNT(*), MAX(SEQUENCE_NBR) as SEQUENCE_NBR
FROM ALERTS  
WHERE ACKNOWLEDGED = 0 AND
      SRC_EXT = '7878'  
GROUP BY EVENT_ID 
ORDER BY COUNT(*) DESC;
user_11440350
Related