0

I have a strange error first time seing such results maybe someone else also found how to overpass such odd error.

I have a table structure like this :

|id|uid|test1|test2|rid|
 1   1   -     -      1
 2   1   -     -      1
 3   2   -     -      2
 4   2   -     -      2
 5   3   -     -      3
 6   3   -     -      3
 7   4   -     -      4
 8   4   -     -      4
----------------------------

as "-" are varchar data all other columns are integers

My query with the odd results is this :

SELECT COUNT(uid) AS COUNT, 
       id AS ID, 
       uid AS InqID, 
       test1 AS A, 
       test2 AS B 
FROM test_table 
WHERE rid = (X) 
GROUP BY uid 
ORDER BY id DESC

As results i get if (X) = 1 or 3 it shows me the correct last id number (2 id for 1 and 6 id for 3) but if (X) is 2 or 4 it shows me the first in row id number (3 id for 2 instead of 4 and 7 id for 4 instead of 8)

Can anyone tell me why i get the correct results only as singles in rid and not at even numbers of rid column or how at least this query will work as it has to ?

Thank you all in advance

Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
Nocs
  • 71
  • 10
  • For the correct use of the `group by` all field not included on one agregated function should be on the `GROUP BY `, in mysql you are allowed to use this sintaxis, but the returned row is random. – Juan Carlos Oropeza Jan 30 '17 at 14:22
  • @Juan Carlos Oropeza Thanks for the correction and the response. – Nocs Jan 30 '17 at 16:49
  • Possible duplicate of [Retrieving the last record in each group](http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group) – Juan Carlos Oropeza Jan 30 '17 at 18:39

1 Answers1

1

Use MAX(id) instead of ORDER BY.

SELECT COUNT(uid) AS COUNT, MAX(id) AS ID, uid AS InqID, test1 AS A, test2 AS B 
FROM test_table 
WHERE rid = (X) 
GROUP BY uid
McNets
  • 10,352
  • 3
  • 32
  • 61