CODEWITHSUNDEEP
mysqlsql
Phoenix
Phoenix

Reputation: 1075

How to group by two fields altogether in MySQL?

Here is my table:

gw_id |gw_payload |gw_name |gw_ref |gw_time             |gw_created_time     |
------|-----------|--------|-------|--------------------|--------------------|
1     |aaa        |PHP     |0.1.1  |2015-11-11 11:34:41 |2015-11-11 13:59:44 |
2     |bbb        |PHP     |0.1.1  |2015-11-11 11:34:41 |2015-11-11 13:59:57 |
3     |ccc        |RUBY    |0.1.2  |2015-11-10 01:34:41 |2015-11-10 13:59:57 |
4     |ddd        |RUBY    |0.1.4  |2015-11-10 02:34:41 |2015-11-10 16:59:57 |

I want to grab records group by gw_name and I want to get the latest gw_ref and latest gw_time. So I want below:

gw_name |gw_ref_max |gw_time_max         |
--------|-----------|--------------------|
RUBY    |0.1.4      |2015-11-10 02:34:41 |
PHP     |0.1.1      |2015-11-11 11:34:41 |

I'm using this SQL, it works, but I don't think it's correct, I'm worried:

select gw_name, max(gw_ref) as gw_ref_max, max(gw_time) as gw_time_max      
from tbl group by gw_name order by gw_time,gw_created_time desc

So what is correct SQL I should write?

Upvotes: 1

Views: 56

Answers (2)

valex
valex

Reputation: 24144

What if max(gw_ref) is not the latest in the group according to gw_created_time?

Usually you should use ROW_NUMBER() per group to order records inside of each group and then select records with ROW_NUMBER = 1. In MySQL there is no ROW_NUMBER() aggregate function but you can use User-Defined variables in MySQL to emulate ROW_NUMBER() 

select *
from (
   select gw_id,gw_ref,gw_time,gw_created_time,
          @num := if(@grp = gw_name, @num + 1, 1) as row_number,
          @grp := gw_name as dummy
  from tbl,(select @num := 0, @grp := null) as T
  order by gw_created_time DESC
) as x where x.row_number = 1;

SQLFiddle demo

Also look at: How to select the first/least/max row per group in SQL

Upvotes: 1

dady
dady

Reputation: 11

If you need to get latest gw_ref and gw_time corresponding to it, you can use subquery.

select * from (
    select gw_name, gw_ref, gw_time
    from tbl
    order by gw_ref desc, gw_time desc, gw_created_time desc
) as s
group by s.gw_name

Be careful to ordering by gw_ref, which can takes values like 0.1.10 (this is greater than 0.1.2). You can try ordering by SUBSTRING_INDEX.

Upvotes: 1

Related Questions