CODEWITHSUNDEEP
mysql
Mir Abzal Ali
Mir Abzal Ali

Reputation: 579

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'

Upvotes: 1

Views: 14

Answers (1)

sagi
sagi

Reputation: 40481

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 )

Upvotes: 1

Related Questions