从mysql表中获取最新添加的条目

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.

我想从表中得到最新的条目（added_on），其中client_id和financial_year是相同的，但我没有客户端ID或甚至财务年度比较我需要在表中自己比较它们。

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

我得到了这个输出，它对每个client_id和financial_year都是唯一的，但它不是最新的条目

Please help in getting the latest entry.

请帮助获取最新条目。

4 个解决方案

#1

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

#2

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

当您使用group时，必须先使用内部查询，然后才能使用它

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 ;

#3

Try adding

尝试添加

ORDER BY added_on DESC
LIMIT 1

at the end of your query.

在您的查询结束时。

#4

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

#1