ORDER BY COM GROUP BY to get last Row, sql

Asked

Viewed 365 times

2

I am using this SQL code to retrieve data, first it groups then organizes by birth date of Rows.

SELECT U.id, U.born_data, U.bold 
FROM users U GROUP BY U.bold ORDER BY U.born_data DESC

However he makes a reversal, he organizes by date the Rows selected by himself (always the first), but I want him to take the last Rows of each grouping according to the date and after picking everyone make an organization again by the date, always from the most recent date to the most distant.

See an "example"

Table: users

id | bold | born_data 
----------------------
1  | BR   | 20/06/2015
2  | BR   | 22/06/2015
3  | EN   | 01/07/2015

It should return me two groupings, BR and EN however it will pick up according to the latest date of each grouping and after all or be it will show me this: 

COUNT | bold | born_data
------------------------
(1)   | EN   | 01/07/2015  -> A DATA MAIS RECENTE DESSE AGRUPAMENTO é tambem a mais recente de todos os agrupamentos ou seja ela vem primeiro..
(2)   | BR   | 22/06/2015 -> A DATA MAIS RECENTE DESTE AGRUPAMENTO

it is worth mentioning that all the data must be from the last Row, as described in this problem that I found in Maicon’s answer.

" Oops, I’m gonna relive here why I got a problem that I just realized now, the MAX function works perfectly showing the date of the most recent, however it shows only the date of the most recent, I wish that it shows all the latest information. Analyze this, sqlfiddle.com/#! 9/22c35a/2 see that the ids are not the latest."

php mysql sql

  • Can you give an example of the output you want to get? It’s not very clear in your question.

2 answers

4

Experiment using the MAX on date:

SELECT COUNT(u.bold) as COUNT, 
       MAX(U.born_data) as born_data, 
       U.bold 
FROM users U 
GROUP BY U.bold 
ORDER BY U.born_data DESC

Exit

COUNT   born_data               bold
1       July, 01 2015 00:00:00  EN
2       June, 22 2015 00:00:00  BR

Sqlfiddle Example

  • That’s cool, this MAX function is only used for this?

  • 1

    The function MAX is to always get the highest value, in the case of dates is the most recent, there is also the function MIN which does exactly the opposite.

  • Oops, I’m going to relive here why I got a problem that I just realized, the function MAX works perfectly showing the date of the most recent, however it shows only the date of the most recent, I wish it shows all the latest information. Review this, http://sqlfiddle.com/#! 9/22c35a/2 see ids is not of the latest.

0

In response to the update of the question:

Try this way to get the ID corresponding to the most recent date.

SELECT MaxData.bold_count AS COUNT, 
       U.ids, 
       MaxData.born_data, 
       MaxData.bold 
FROM users U 
INNER JOIN 
(
    SELECT bold,
           COUNT(bold) as bold_count, 
           max(born_data) born_data
    FROM users
    GROUP BY bold
) MaxData
   ON MaxData.bold = U.bold
  AND MaxData.born_data = U.born_data 
ORDER BY MaxData.born_data DESC

Sqlfiddle

Browser other questions tagged php mysql sql

You are not signed in. Login or sign up in order to post.