Mir Abzal Ali Mir Abzal Ali - 6 months ago 8
MySQL Question

How can I use Max counted id in where clause as parameter in Mysql

Here the 1st query that returns

TraineeID
which max by count. I need to use this
TraineeID
in 2nd query as a parameter in a where clause. Is there any way to get this done. Thank you in advance.

QUERY 1:

SELECT TraineeID, COUNT(TraineeID) as maxfinger
FROM tbl_raw_attendance WHERE date(CreatedTime)='2016-04-13'
GROUP by TraineeID ORDER by maxfinger DESC
LIMIT 1


QUERY 2:

SELECT @n := @n + 1 RowNumber, t.*
FROM (select @n:=0) initvars, tbl_raw_attendance t
WHERE date(t.CreatedTime)='2016-04-13'

Answer

You can do it with a sub query:

SELECT @n := @n + 1 RowNumber, t.*
FROM (select @n:=0) initvars, tbl_raw_attendance t
WHERE date(t.CreatedTime)='2016-04-13'
   AND t.TraineeID = (SELECT TraineeID FROM tbl_raw_attendance
                      WHERE date(CreatedTime)='2016-04-13' 
                      GROUP by TraineeID ORDER by COUNT(TraineeID) DESC
                      LIMIT 1 )
Comments