Mysql sort by date and group by results differently

Question

My table structure as follows

id  parent  last_date     sub_type  
----------------------------------
11  9       2017-02-28    1101 
10  9       2016-08-26    1101
8   1       2017-02-20    1101  
12  12      2016-08-31    1102      
14  12      2016-12-20    1102      
9   9       2016-12-31    1101  
13  12      2017-03-23    1102  
2   1       2017-01-25    1101  
1   1       2016-12-31    1101

i want to fetch rows for each sub_type based the date (longest first) . i tried following query

SELECT * FROM mytable GROUP BY sub_type ORDER BY ISNULL(last_date) DESC, last_date DESC

and it results

id  parent  last_date   sub_type    
--------------------------------
1   1       2016-12-31  1101    
12  12      2016-08-31  1102

But i expect below result .

id  parent  last_date   sub_type    
--------------------------------   
13  12      2017-03-23  1102 
11  9       2017-02-28  1101

Please guide me to get above result .

EDIT:

last_date may have NULL value which will max precedence over dated entries. Thatswhy i choose ISNULL DESC order.

`SELECT sub_type, MAX(last_date) FROM mytable GROUP BY sub_type` ? — Prabowo Murti, Feb 27 '17 at 07:06
What if there are more than one null records exist? Which record do you want to fetch? — Blank, Feb 27 '17 at 07:41
@Forward . there is no 2 entries with same last date,parent,subtype. for ex : No duplicate for parent: 9, last_date : 2017-02-28,sub_type: 1101. — ArK, Feb 27 '17 at 07:46
@PaulSpiegel yes id is primary key. here i showed limited columns,rows only not to confuse too much. But concept is same fetching an entry from each group with max date (including NULL dates) — ArK, Feb 27 '17 at 07:50

score 1 · Answer 1 · edited May 23 '17 at 11:53

This is a typical question fetching one record in each group by some aggregation. Try this:

select
    mytable.*
from mytable
join (
    select max(last_date) as last_date, sub_type from mytable group by sub_type
) t1 on mytable.sub_type = t1.sub_type and mytable.last_date = t1.last_date

See this article How to select the first/least/max row per group in SQL.

and Related link at right:

Retrieving the last record in each group.

And demo in sqlfiddle.

Edit:

if there is no 2 same last date and null precedence, then try this:

select
    mytable.*
from mytable
join (
    select 
        max(last_date) as last_date, 
        max(isnull(last_date)) nulled, 
        sub_type 
    from mytable 
    group by sub_type
) t1 on mytable.sub_type = t1.sub_type
and (t1.nulled and mytable.last_date is null or (t1.nulled <> 1 and mytable.last_date = t1.last_date))

also demo in sqlfiddle.

Thanks a lot for the answers . i will try your suggestions . — ArK, Feb 27 '17 at 07:53

Ullas · Answer 2 · 2017-02-27T07:18:07.143

1

You can also do it by giving a row number based on the sub_type column and in the descending order of the last_date column.

Query

select t1.`id`, t1.`parent`, t1.`last_date`, t1.`sub_type` from 
(
    select `id`, `parent`, `last_date`, `sub_type`, 
    (
        case `sub_type` when @A 
        then @R := @R + 1 
        else @R := 1 and @A := `sub_type` end 
    ) as `rn`
    from `your_table_name` t, 
    (select @R := 0, @A := '') r 
    order by `sub_type`, `last_date` desc 
)t1 
where t1.`rn` = 1;

Sql Fiddle demo

edited Feb 27 '17 at 07:18

answered Feb 27 '17 at 07:11

Ullas

11,450
4
33
50

Thanks for your answer . i will try your suggestions – ArK Feb 27 '17 at 08:04

Paul Spiegel · Accepted Answer · 2017-02-27T07:54:35.733

1

You can use a correlated subquery in the WHERE clause with ORDER BY and LIMIT 1 to find the id of the row you are looking for.

SELECT * 
FROM mytable t1
WHERE id = (
    SELECT id
    FROM mytable t2
    WHERE t2.sub_type = t1.sub_type
    ORDER BY ISNULL(last_date) DESC, last_date DESC, id DESC
    LIMIT 1
)

Demo: http://rextester.com/DSPH11524

Note: sub_type should be indexed.

edited Feb 27 '17 at 07:54

answered Feb 27 '17 at 07:34

Paul Spiegel

30,925
5
44
53

Thanks a lot for the answer . i will try your suggestion – ArK Feb 27 '17 at 08:05

score 0 · Answer 4 · edited Feb 27 '17 at 07:13

0

You have written a wrong query.

Specify Where condition after where clause

Below query will give your expected result.

SELECT id,parent,max(last_Date),sub_type FROM mytable   GROUP BY sub_type

edited Feb 27 '17 at 07:13

Ullas

11,450
4
33
50

answered Feb 27 '17 at 07:13

Ajay Singh

1

@ Ajay Singh thanks for the note. its type error. corrected it now – ArK Feb 27 '17 at 07:14
`id` and `parent` columns are not with aggregate functions and not in group by clause also. – Ullas Feb 27 '17 at 07:14
@ullas in mysql its not necessary to specify all columns for group by , It will group on the group by expression – Ajay Singh Feb 27 '17 at 07:19
@Ark This query will get the exact result set you required. add order by sub_type desc in the end as result you expected – Ajay Singh Feb 27 '17 at 07:20
@AjaySingh : With your query OP will get the result set what he is getting now. But op expects another result set. – Ullas Feb 27 '17 at 07:23
@AjaySingh please note by EDIT also. Last Date may have NULL value. MAX wont work with NULL. – ArK Feb 27 '17 at 07:25

Mysql sort by date and group by results differently

4 Answers4