Grouping by multiple columns in SQL

Question

I am trying to bring through the site.Site_Name, for each hive.hiveno and it's max(hiverdg.invdate). Running the code below doesn't work because site.Site_Name is not aggrigated. If I add site.Site_Name to the Group By, the code runs, but the ouput displays the results repeated, once for each site.Site_Name

select site.Site_Name ,hive.hiveno, max(hiverdg.invdate)
from hiverdg 
        inner join hive
        on        hiveRdg.hive_Link = hive.hive_Link
        inner join Customer
        on        customer.Customer_Link = hive.Customer_Link
        inner join site
        on        site.Customer_Link = customer.Customer_Link        
where 
(hiverdg.xtype = 'N'
and customer.CustomerName = 'Cust1')
or
(hiverdg.xtype = 'A'
and customer.CustomerName = 'Cust1')
group by hive.hiveno

which site you'd like to see? the one associated with `max(invdate)` ? — Vland, Aug 29 '14 at 10:14
group by hiveno, select hiveno and max(invdate) as you did. then use a subquery/join to get the site_name where the date is equal to max(invdate) — Vland, Aug 29 '14 at 10:21
what you need is probably similar to this solution: http://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-another-column-in-sql?rq=1 — Vland, Aug 29 '14 at 10:24

score 1 · Accepted Answer · answered Aug 29 '14 at 10:46

The easiest way to do this, with your query, is the substring_index()/group_concat() trick:

select substring_index(group_concat(s.Site_Name order by rdg.invdate desc separator '|'
                                   ), '|', 1
                      ) as SiteName,
       h.hiveno, max(rdg.invdate)
from hiverdg rdg inner join
     hive h
     on rdg.hive_Link = h.hive_Link inner join
     Customer c
     on c.Customer_Link = h.Customer_Link inner join
     site s
     on s.Customer_Link = c.Customer_Link        
where rdg.xtype in ('N', 'A') and c.CustomerName = 'Cust1')
group by h.hiveno;

I also made the following changes to your query:

Introduced table aliases, to make the query easier to write and to read.
Changed the where to use in, simplifying the logic.

Grouping by multiple columns in SQL

1 Answers1