ORDER BY datetime but keep records with same TITLE on top of each other

user_4094231 0
Viewed 56

I have table like

test
+---------------------+-------+
| date_in             | title |
+---------------------+-------+
| 2018-01-01 00:00:00 | foo   |
| 2018-01-02 00:00:00 | bar   |
| 2018-01-03 00:00:00 | man   |
| 2018-01-04 00:00:00 | foo   |
| 2018-01-05 00:00:00 | test  |
+---------------------+-------+

my desired result is

+---------------------+-------+
| date_in             | title |
+---------------------+-------+
| 2018-01-05 00:00:00 | test  |
| 2018-01-04 00:00:00 | foo   | -- see this
| 2018-01-01 00:00:00 | foo   | -- see this
| 2018-01-03 00:00:00 | man   |
| 2018-01-02 00:00:00 | bar   |
+---------------------+-------+

I have tried below query and other several queries but it doesn't work

select * from test order by title, date_in desc

Here http://sqlfiddle.com/#!9/1dd33f/4 SQLFiddle with schema.

mysql
2 Answers

You are attempting to order by three columns.

  1. the date_in for which each title first appeared.
  2. the title itself
  3. the date_in for each row

You need to generate the first of these three in your query. To do this you write a subquery and join it, as if it were a virtual table, to your table.

This subquery does the trick. It gives one row per title, showing the first date the title appeared. (http://sqlfiddle.com/#!9/1dd33f/23/0)

select min(date_in) firstdate, 
       title
  from test
 group by title

You join that to your main table. Here's the whole query. (http://sqlfiddle.com/#!9/1dd33f/22/0)

select detail.* 
  from test detail
  join  (select min(date_in) firstdate, 
                title
            from test
           group by title
         ) firstdate on detail.title = firstdate.title 
  order by firstdate.firstdate, detail.title, detail.date_in

If the subquery is slow try creating an index on (title, date_in). That kind of query uses a so-called loose index scan and should be reasonably fast.

ALTER TABLE test ADD INDEX test_title_date (title,date_in);
user_2056080

Try this query,this is using subquery,if that was not a problem ,you can try this 

SELECT test.* FROM (select MAX(date_in) as temp_date_in,title from
test GROUP BY title order by  date_in desc) as temp JOIN test ON
temp.title = test.title ORDER BY temp.temp_date_in DESC,test.date_in DESC

http://sqlfiddle.com/#!9/1dd33f/72

user_16828260
Related