CODEWITHSUNDEEP
phpmysql
swap
swap

Reputation: 63

get the latest added entry from mysql table

I wanted to get the latest entry (added_on) from the table where client_id and financial_year are same, but i don't have the client id or even financial year to compare i need to compare them in the table it self.

enter image description here

i used following query:

SELECT * 
  FROM file_mgmt fm
     , client c
     , cupboard cb 
  where fm.client_id = c.client_id 
   AND fm.cupboard_id = cb.cupboard_id
   AND fm.status = 'Active' 
 GROUP 
    BY fm.client_id
     , fm.financial_year

and i got this output, which is unique against each client_id and financial_year but its not the latest entry

enter image description here

Please help in getting the latest entry.

Upvotes: 0

Views: 61

Answers (4)

Dr. X
Dr. X

Reputation: 2930

SELECT * 
  FROM file_mgmt fm
     , client c
     , cupboard cb 
  where fm.client_id = c.client_id 
   AND fm.cupboard_id = cb.cupboard_id
   AND fm.status = 'Active' 
 GROUP 
    BY fm.client_id
     , fm.financial_year
ORDER BY added_on DESC
LIMIT 1

Upvotes: 0

jeprubio
jeprubio

Reputation: 18012

Try adding

ORDER BY added_on DESC
LIMIT 1

at the end of your query.

Upvotes: 0

B. Desai
B. Desai

Reputation: 16446

As you are using group by you have to use inner query first then you can use it

SELECT fm.*,c.*,cb.* FROM file_mgmt fm, client c, cupboard cb 
JOIN (SELECT file_mgmt_id,client_id, financial_year,max(added_on) 
FROM file_mgmt GROUP BY client_id,financial_year) as fm1 
ON fm.file_mgmt_id = fm1.file_mgmt_id
where fm.`client_id` = c.`client_id` AND 
fm.`cupboard_id` = cb.`cupboard_id` AND fm.status = 'Active'

EDIT

try this simpler query instead

   SELECT fm.*,max(added_on) FROM file_mgmt fm, 
   client c , cupboard cb 
   where fm.client_id = c.client_id 
   AND fm.cupboard_id = cb.cupboard_id
   AND fm.status = 'Active'  
   group by client_id,financial_year ;

Upvotes: 0

Pattatharasu Nataraj
Pattatharasu Nataraj

Reputation: 248

Hope this will help 

  SELECT * FROM file_mgmt fm, client c, cupboard cb where fm.`client_id` = c.`client_id` AND fm.`cupboard_id` = cb.`cupboard_id` AND fm.status = 'Active' 
    GROUP BY fm.`client_id`, fm.`financial_year`
    ORDER BY id DESC
    LIMIT 1

Upvotes: 1

Related Questions