0

I have a below query and it's working now I have to show the latest records of each user. I am using below query.

 SELECT 
  * 
FROM 
  company21 as c 
  inner join industryList as ind on c.industry = ind.industry_id 
  left join company21_badge_list as bd on c.badge_logo = bd.bd_id 
  left JOIN company21_login as l on c.login_id = l.login_id 
WHERE 
  c.company_id =(
    SELECT 
      MAX(company_id) AS c_id 
    FROM 
      company21 
    where 
      login_id = 4   // here I used 4 to display the records of 4th user
    GROUP BY 
      login_id DESC
  ) 
  and c.is_active = 1 
  and c.is_saved = 1 
  and c.send_for_approval = 1

I have to show all the records of each user.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
user9437856
  • 2,360
  • 2
  • 33
  • 92
  • I tried the reference answer before asking i am getting the issue in the subquery . syntax error – user9437856 May 26 '21 at 20:20
  • "_As of MySQL 8.0.13, the GROUP BY extension is no longer supported: ASC or DESC designators for GROUP BY columns are not permitted._" https://dev.mysql.com/doc/refman/8.0/en/select.html – Bill Karwin May 26 '21 at 20:51
  • You don't need the GROUP BY DESC anyway, since the order in the subquery should not matter. Also you need `c.company_id IN (...)`, not `=`. – Bill Karwin May 26 '21 at 20:52
  • And besides that, since you are using MySQL 8.0 (hence the syntax error), you should use window functions for this task. – Bill Karwin May 26 '21 at 20:53
  • @BillKarwin, apologize for the late reply. I have to show all the latest rows of each user. I don't have much knowledge of Mysql. I will learn this.. Can you suggest to me where should I have to use the window function? – user9437856 May 27 '21 at 01:59
  • I have used login_id=4 in the query and I am getting the correct output of the single user. Now I have to fetch the all the user information – user9437856 May 27 '21 at 02:01
  • @BillKarwin, I tried the duplicate link answer but I am getting some issue in the join. I mean I am already using 3 joins and I am a little bit confused about where should I have to use the next join. – user9437856 May 27 '21 at 02:06
  • I tried the below query.. I am getting the output now.. is this the best query SELECT * FROM company21 as c inner join industryList as ind on c.industry = ind.industry_id left join company21_badge_list as bd on c.badge_logo = bd.bd_id left JOIN company21_login as l on c.login_id = l.login_id inner join (select max(company_id) as maxid from company21 group by login_id) as b on c.company_id = b.maxid and c.is_active = 1 and c.is_saved = 1 and c.send_for_approval = 1 – user9437856 May 27 '21 at 02:30

0 Answers0