ORDER BY WITH GROUP BY to get last row, sql

Question

ORDER BY WITH GROUP BY to get last row, sql

Navigation

#1 by (4 votes)
#2 by (0 votes)

2

I am using this SQL code to pick up data, first it groups then it organizes by the 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

In the meantime it does an inversion, it organizes by date the rows selected by itself (always the first ones), but I want it to take the last rows of each grouping according to the date and after picking all make an organization again by the date, always from the most recent to the most distant date.

Look at 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 most recent date of each grouping and after all or that is 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

Note that all data must be from the last row, as described in this problem that I encountered in Maicon's response.

"Oops, I'm going to relive here because I got a problem that I just noticed now the MAX function works perfectly showing the date of the recent, but it only shows the date of the most recent, I it shows all the latest information. Analyze this, sqlfiddle.com/#!9/22c35a/2 see that the ids is not the latest. "

php mysql sql

asked by anonymous 01.07.2015 / 16:17

2 answers

How to get 40px X 40px from every image? Final ASP.NET WebForms?

score 4 · Answer 1

Try using 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

Output

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

SqlFiddle Example

score 0 · Answer 2

In response to the question update:

Try 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