-1

I have a following table

-----------------------------------
Date        |      Prefilling  |  Ref
-----------------------------------
10/10/2017  |       5          |  dinesh
17/10/2017  |       8          |  Ajay  
07/05/2017  |       5          |  Aarthi
01/01/2018  |       8          |  Kumar 
-----------------------------------

How to perform order by and group by in same time? I am using following query but its not giving actual result

  SELECT * FROM (  
        select * from table order by str_to_date(date,'%d/%m/%Y') DESC  
    ) AS tmp_table group by  Prefilling

and i am expecting the result, along with Ref

Array 
(
  [5] =>  10/10/2017, dinesh
  [8]  => 01/01/2018,  kumar
}
James Z
  • 12,209
  • 10
  • 24
  • 44
Thirukumaran
  • 27
  • 2
  • 1
    I'm wondering why you're storing dates as strings, when it'd be easier to query storing data from mysql's built-in date functions. It's not to late to change your design if you're not too far into this. – Funk Forty Niner Feb 09 '18 at 02:31
  • @Bill you've a gold on php, why not close it if you think it's a possible duplicate? Edit: ;-) – Funk Forty Niner Feb 09 '18 at 02:47
  • @FunkFortyNiner I sometimes hesitate to close questions, if there's any slim possibility that they are not an exact duplicate. But you're right, in this case it's a classic greatest-n-per-group question. There are hundreds of those answered already. – Bill Karwin Feb 09 '18 at 02:49
  • @Bill and an interesting description it is for that tag; good choice. – Funk Forty Niner Feb 09 '18 at 02:50

1 Answers1

1

Based from the result you've showed, you wanted to get the latest date for each Prefilling. You can so this by using MAX() and order it using the largest value.

SELECT  Prefilling, MAX(STR_TO_DATE(`date`,'%d/%m/%Y')) AS MAX_DATE
FROM    TableName
GROUP   BY Prefilling
ORDER   BY MAX_DATE

Here's a Demo

Below is the updated answer based from your updated question. You can separately retrieve the latest date for each Prefilling inside a subquery and join the result with the original table itself provided that it matches on both conditions - Prefilling and latest date to get all the columns.

SELECT  a.*
FROM    TableName a
        INNER JOIN
        (
            SELECT  Prefilling, MAX(str_to_date(date,'%d/%m/%Y')) AS MAX_DATE
            FROM     TableName
            GROUP   BY Prefilling
        ) b ON a.Prefilling = b.Prefilling
                AND str_to_date(a.date,'%d/%m/%Y') = b.MAX_DATE
ORDER   BY b.MAX_DATE

And a Demo

John Woo
  • 258,903
  • 69
  • 498
  • 492