DB2 Group By but table 2 makes duplicates

Question

I'm trying to query the iSeries DB2 v6r1m0. I usually test my SQL statements in System i Navigator before using them in ADO.Net.

I've traced my issue down to this, but I'm not sure how to fix it.

SELECT 
    a.id
    , a.otherstuff
    , MAX(a.date || ' ' || a.time) as adatetime /* I'm sure it's not this line */
    , b.id
    , b.city 
    , b.state
    , MAX(b.date || ' ' || b.time) AS bdatetime
FROM 
    table1 a

INNER JOIN
    table2 b
ON a.id = b.id

GROUP BY
   a.id, a.otherstuff, b.id, b.city, b.state

What happens is that it shows all of the b.cities and b.states, even though I just want the b.city and b.state with the max value.

a.id    a.otherstuff    a.adatetime     b.id    b.city          b.state b.datetime  
a.dup1  a.dup1          a.dup1          b.dup1  San Francisco   CA      1-Jan   1:00
a.dup1  a.dup1          a.dup1          b.dup1  Sacramento      CA      1-Jan   2:00
a.dup1  a.dup1          a.dup1          b.dup1  other cities    WA      11-Jan  3:00
a.dup2  a.dup2          a.dup2          b.dup2  San Francisco   CA      11-Jan  1:00
a.dup2  a.dup2          a.dup2          b.dup2  Sacramento      CA      11-Jan  2:00
a.dup2  a.dup2          a.dup2          b.dup2  other cities    WA      11-Jan  3:00

Why is this happening?

Answer 1

Using GROUP BY, your result set will hold a (one) line for each unique combination of values in the columns listed. So, unless all selected and grouped by columns in table1 hold identical values, you will end up with more than a single row even without joining a second table.

If you just want the b.city and b.state with the max value, you need to single out the appropriate row(s) using a WHERE condition - as suggested by @mustaccio.

Something along

SELECT
  a.id
  , a.otherstuff
  , a.date_time
  , b.city
  , b.state
  , b.date_time
FROM
  table1 a
INNER JOIN
  table2 b
ON
  a.id = b.id
WHERE
  b.date_time = (SELECT MAX(date_time) FROM table2)
;

should provide that.

If you want rather the maximum date_time value from table1 in your result - no matter from which record it is coming, try

SELECT
  a.id
  , a.otherstuff
  , (SELECT MAX(date_time) FROM table1) a_max_date_time
  , b.city
  , b.state
  , b.date_time b_date_time
FROM
  table1 a
INNER JOIN
  table2 b
ON
  a.id = b.id
WHERE
  b.date_time = (SELECT MAX(date_time) FROM table2)
;

See it in action: SQL Fiddle. (Sorry - no DB2 in SQL Fiddle; and just a single date_time column each.)

Please comment if and as further detail / adjustment is required. (Sample data and expected output would be particularly helpful...)

Answer 2

It's hard with pseudocode like this, but I think you'll need to join not directly to table2, but a derived query based on table2.

...
FROM 
    table1 a
    INNER JOIN
    (
    SELECT
        table2.*
    FROM
        (
        SELECT
            table2.id,
            MAX(table2.datetime) AS MaxDateTime
        ) DerivedMax
        LEFT OUTER JOIN
        table2 ON table2.datetime = DerivedMax.MaxDateTime AND table2.id = DerivedMax.id
    ) DerivedOnlyRowsFromTable2YouCareAbout ON a.id = DerivedOnlyRowsFromTable2YouCareAbout.id

Depending on how the data exists in your table1, it may need the same treatment. If this isn't enough to be useful, I would ask you make an sqlfiddle with actual structure and data to play with. As I said, pseudocode can leave something to be desired.

Answer 3

You said "I just want the b.city and b.state with the max value".

I take that to mean you only want one row in your result set -- the one with the highest value of some column, and no other records for anything else.

You might simply add an ORDER BY and FETCH FIRST clause at the end

SELECT 
       a.id
       , a.otherstuff
       , MAX(a.xdate || ' ' || a.xtime) as adateinfo /* I'm sure it's not this     line */
       , b.id
       , b.city 
       , b.state
       , MAX(b.xdate || ' ' || b.xtime) as bdateinfo
  FROM table1 a
  JOIN table2 b
     ON a.id = b.id
  GROUP BY
     a.id, a.otherstuff, b.id, b.city, b.state, b.bdateinfo
  ORDER BY 
     bdateinfo desc
  FETCH FIRST ROW ONLY

However, rather than join and group all those records, it's probably much more efficient to narrow it down to a single record in table2 first, especially if the files are large.

SELECT a.id 
     , a.otherstuff
     , MAX(a.xdate || ' ' || a.xtime) as adateinfo 
     , b.city 
     , b.state
     , b.bdateinfo  
  FROM table1 a
  JOIN (
         SELECT id
               ,city
               ,state
               ,xdate
               ,xtime
               ,(xdate || ' ' || xtime) AS bdateinfo
           FROM table2 i
           ORDER BY xdate DESC, xtime DESC
           FETCH FIRST ROW ONLY
       ) AS b
       ON a.id = b.id
GROUP BY
   a.id, a.otherstuff, b.city, b.state, b.bdateinfo

So you should get something like

ID   OTHERSTUFF        ADATEINFO   CITY             STATE    BDATEINFO
27   whatever stuff    1/05 3:00   San Francisco    CA       1/11 1:00

Hopefully, this is what you wanted.

---

Footnote: I dropped b.id from the results, seeming redundant and extraneous, since it must be equal to a.id