Auj Auj - 6 months ago 14
SQL Question

Mysql query showing extra fields

I am trying this query:

SELECT * FROM heath_check where cid = '1' and eid in('3','5','7','1','6')


My table structure:

enter image description here

I want distinct
eid
but all other data as it is. For example I have two entries with an
eid
of
1
my query fetched both, but I want one which is in the second column.

enter image description here

Answer
SELECT * 
FROM heath_check AS hc
INNER JOIN (
   SELECT MAX(id) AS lastId 
   FROM heath_check 
   WHERE cid = '1' and eid in('3','5','7','1','6')
   GROUP BY eid) AS lastIDs
ON hc.id = lastIDs.lastId
;

You need a subquery, like the above, to find the records you want for each value. If you had wanted the first ones, you could use MIN(id) instead; if you cannot count on sequential ids, it becomes much more complex with use of potentially non-unique timestamps (if they are even available).

Comments