group by all columns from one table

sql join group-by db2

14,974

No, you don't have to type them all, because you don't need to use group by. Instead, use a correlated subquery:

select c.* ,
       (select max(TLO.BILL_DATE)
        from TLORDER TLO
        where TLO.CUSTOMER = c.CLIENT_ID or
              TLO.ORIGIN = c.CLIENT_ID or
              TLO.DESTINATION = c.CLIENT_ID
       )
from client c;

If you used group by, then you would have to list all the columns. Do note that ANSI SQL has support for using only a primary or unique key in this case. So, this would be ANSI-compliant:

select c.*, max(TLO.BILL_DATE)
from client c left join
     TLORDER TLO
     on TLO.CUSTOMER = c.CLIENT_ID or
        TLO.ORIGIN = c.CLIENT_ID or
        TLO.DESTINATION = c.CLIENT_ID
group by c.c_id;

I don't believe that DB2 supports this construct, although a few other databases do.

14,974

Author by

Jomathr

Updated on June 29, 2022

Comments

Jomathr almost 2 years
I have a simple query:
```
select c.* , max(TLO.BILL_DATE)
from

client c
left outer join TLORDER TLO
on TLO.CUSTOMER = c.CLIENT_ID
OR TLO.ORIGIN = c.CLIENT_ID
OR TLO.DESTINATION = c.CLIENT_ID

GROUP BY c.*
```
The query itself is pretty straightforward however the client table contains over 200 fields and I need to retrieve them all.

is there a way to do something like
```
GROUP BY c.*
```
or do I have to type all 200+ GROUP_BY?

Thank you
- Daniel Marcus almost 6 years
  
  You can select the columns off the systables and then dynamically create your code off that but it will probably take longer than just writing out the fields to begin with...
- Error_2646 almost 6 years
  
  I'd just write them out ... you don't have to pay per line of code. Like Daniel said, you could go dynamic but that is more trouble than it's worth unless you are going to have to run many times and the aggregate column list is going to vary in a way you can code for.
- Clockwork-Muse almost 6 years
  
  Side note - you almost never want to GROUP BY more than about ~5 columns, which will almost always be from the same table, as well. Doing so is usually a sign that either your data model needs some work, or you might be approaching the problem in an obtuse way.
- Jomathr almost 6 years
  
  @Clockwork-Muse Thank you for your input, I will take a note of it as I am able to fiddle with SQL and make the data model in my head of what I want but I am far from beeing an expert on the SQL language/subtilities
Jomathr almost 6 years

Tried your solution and as you suspected DB2 doesn't support it, but great idea for other DB
Jomathr almost 6 years

The query does run but it returns a lot of duplicates, I will try fiddling with it to see if I can come up with a solution without duplicate. Thank you Vernon
Paul Vernon almost 6 years

Opps. I had a typo in the 2nd join - I grouped by an extra column. I've fixed it now
Gordon Linoff almost 6 years

@Jomathr . . . but the first query should be fine in DB2.
Jomathr almost 6 years

I check it out and the behavior of how it fetch the data is not correct, it is suppose to return one date per client and I end up with 531 results out of 25 000 for the date field, I am pretty sure there would be a fix but I figured it out by using Gordon's solution.
Jomathr almost 6 years

You sir are absolutely right and I was able to get the result I wanted thx to your help!
Paul Vernon almost 6 years

My fixed code should work. If you have duplicate rows in your Client table then de-dup those with a DISTINCT. Oh, just seen another flaw.. MAX might return NULL s ... two ticks
Paul Vernon almost 6 years

fixed by adding COALESCE on the dates
Paul Vernon almost 6 years

was my answer useful in the end?