SQL query returning duplicate rows with join

Question

Similar question had been ask multiple times, but none of the answers help in this situation, so asking the question again

I have a table with this following structure(yeah no unique key here):

---------------------------------------------------------------
|a_number | a_status | b_status | ref_id | timepublished       |
|(text)   | (text)   | (text)   |(text)  | (datetime)          |
---------------------------------------------------------------
|1        | U        | U        | re1    | 2016-08-12 13:24:25 |
|1        | P        | U        | re1    | 2016-08-12 13:24:35 |
|1        | P        | P        | re1    | 2016-08-12 13:24:45 |
|2        | U        | U        | re2    | 2016-08-12 12:24:30 |
|2        | U        | F        | re1    | 2016-08-12 12:24:45 |
|4        | U        | U        | re3    | 2016-08-12 13:24:30 |
|4        | U        | U        | re4    | 2016-08-13 15:24:30 |
---------------------------------------------------------------

Now the problem I am trying to solve is to get the latest states for each a_number. So the CORRECT output should be:

---------------------------------------------------------------
|1        | P        | P        | re1    | 2016-08-12 13:24:45 |
|2        | U        | F        | re1    | 2016-08-12 12:24:45 |
|4        | U        | U        | re3    | 2016-08-12 13:24:30 |
|4        | U        | U        | re4    | 2016-08-13 15:24:30 |
---------------------------------------------------------------

And the query I have is :

SELECT af.a_number
     , af.a_status
     , af.b_status
     , af.ref_id
     , af.timepublished
FROM af_biz af
 JOIN
 (SELECT MAX(timepublished) timepublished, ref_id
     FROM af_biz GROUP BY ref_id) tmp 
 ON tmp.timepublished = af.timepublished AND tmp.ref_id = af.ref_id
ORDER BY af.a_number;

But the result I get contain incorrect output like this (Notice that the time is max timepublished but statuses are different):

---------------------------------------------------------------
|1        | P        | U        | re1    | 2016-08-12 13:24:45 |
|1        | P        | P        | re1    | 2016-08-12 13:24:45 |
|2        | U        | U        | re2    | 2016-08-12 12:24:45 |
|2        | U        | F        | re1    | 2016-08-12 12:24:45 |
|4        | U        | U        | re3    | 2016-08-12 13:24:30 |
|4        | U        | U        | re4    | 2016-08-13 15:24:30 |
---------------------------------------------------------------

Anyone has any idea on what could be wrong with my query ??

@Sovon, which is invalid GROUP BY - not allowed in newer MySQL versions... — jarlh
– jarlh, Commented Aug 17, 2016 at 7:04
@Sovon, even though it's valid, shouldn't be done ... that works in MySQL cause of the extension provided by MySQL but will not work in any other RDBMS. — Rahul
– Rahul, Commented Aug 17, 2016 at 7:06
@Rahul, isn't every RDBMS has this syntax? I checked oracle, they also have it. Also, the question is related to MySql. — Sovon
– Sovon, Commented Aug 17, 2016 at 7:11
@Sovon, NO, the point is wrong group by ... you should include all columns present in select list to group by and not only a specific column. That's what the comments points put. — Rahul
– Rahul, Commented Aug 17, 2016 at 7:13

1000111 · Accepted Answer · 2016-08-17 07:13:36Z

1

Please give this a try:

SELECT 
   af.a_number
 , af.a_status
 , af.b_status
 , af.ref_id
 , af.timepublished
FROM af_biz af
INNER JOIN 
(
    SELECT 
     a_number,
     MAX(timepublished) max_timepublished
    FROM af_biz 
    GROUP BY a_number
) AS t
ON af.a_number = t.a_number AND af.timepublished = t.max_timepublished
ORDER BY af.a_number

First you needed to group by a_number. ^{(Since you want result for each a_number)}

Second later needed to join on matching timepublished and a_number ^{(not ref_id)}

edited Aug 17, 2016 at 7:13

answered Aug 17, 2016 at 7:07

1000111

13.5k2 gold badges32 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Abhijit Chowdhury · Accepted Answer · 2016-08-17 08:12:28Z

1

Old fashioned but I have used sub query

Here it is :

select 
   testtable.a_number
  ,testtable.a_status
  ,testtable.b_status
  ,testtable.timepublished
 from testtable
  inner join
 (   select
       a_number
      ,max(timepublished) as date
     from
       testtable 
     group by
       a_number ) as anumberWithMaxDate
 on 
   testtable.a_number = anumberWithMaxDate.a_number and 
   testtable.timepublished = anumberWithMaxDate.date

answered Aug 17, 2016 at 8:12

Abhijit Chowdhury

2222 silver badges8 bronze badges

Comments

RAY · Accepted Answer · 2016-08-17 07:12:12Z

0

SELECT af.a_number, af.a_status, af.b_status, af.ref_id, af.timepublished
FROM af_biz af
 JOIN
 (
SELECT MAX(timepublished) timepublished, 
afb.a_number+ afb.a_status+ afb.b_status+ afb.ref_id as key
     FROM af_biz afb 
GROUP BY afb.a_number+ afb.a_status+ afb.b_status+ afb.ref_id
) tmp 
 ON tmp.timepublished = af.timepublished 
AND tmp.key = (af.a_number+ af.a_status+ af.b_status+ af.ref_id)
ORDER BY af.a_number;

answered Aug 17, 2016 at 7:12

RAY

2,3092 gold badges19 silver badges20 bronze badges

Comments

Jakumi · Accepted Answer · 2016-08-17 09:04:49Z

0

Your query is expressing something different from what you want. And apparently you have some misunderstanding about joins. To explain your current query:

You generate a intermediate table of the form (refid,maxtimepublished), and for every refid you have exactly one entry. This is your right table in the join. The Join will match every left row (original table) with every right row, where the join condition is fulfilled.

Now, the max time for refid=1 is 2016-08-12 12:24:45, so every row of the left table (original) with refid=1 and timepublished='2016-08-12 12:24:45' will end up in the result. Which is the third and the fifth row. Repeat for refid=2 and the fourth row will be output as well. With refid=3 the last row will be ouput.

Those are 4 rows, which doesn't match the output you claim you got. But that's probably just an error.

I think you have to make some assumptions about your data. For example:

for every a_number there will be at most one entry for any given time (independent of ref_id)

In that case you could replace every ref_id in your query with a_number and be done with it. (which is also 1000111's and Abhijit Chowdhury's answer)

answered Aug 17, 2016 at 9:04

Jakumi

8,3842 gold badges19 silver badges34 bronze badges

1 Comment

Vivek Over a year ago

I cannot make assumption that there would be atmost one entry (making that assumption makes it a unique key which make the problem lot more easier). I have edited my question to reflect that

Sindhu · Accepted Answer · 2016-08-17 11:15:09Z

0

select 
    A_NUMBER,A_STATUS,B_STATUS,REF_ID,TIMEPUBLISHED
from  
    TABL
where 
    TIMEPUBLISHED in (select 
                          b.TIMEPUBLISHED
                      from 
                          (select 
                               A_NUMBER,max(TIMEPUBLISHED) as TIMEPUBLISHED
                           from TABL
                               group by A_NUMBER) b )
;

answered Aug 17, 2016 at 11:15

Sindhu

4161 gold badge4 silver badges16 bronze badges

Collectives™ on Stack Overflow

SQL query returning duplicate rows with join

5 Answers 5

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related