CODEWITHSUNDEEP
mysqlsql
Björn C
Björn C

Reputation: 4008

Complex mySQL query, find dupes, order by date and by group

I´m trying to figure out how to write my query.

First of all i´m looking for dupes in a table:

$query = "SELECT datum, element_skanning.objekt_element 
          FROM element_skanning
          INNER JOIN (SELECT objekt_element
                      FROM   element_skanning
                      GROUP  BY objekt_element
                      HAVING COUNT(id) > 1) dup
                      ON element_skanning.objekt_element = dup.objekt_element;                                
          ";

while($row = $stmt->fetch()){
     echo "<tr>";
       echo "<td>".$row['objekt_element']."</td>";
       echo "<td>".$row['datum']."</td>";
     echo "</tr>";
}

Results looks like: enter image description here

You can see there is alot of dupes.
Now i´d like to ORDER these result.

I´d like them by date.. but also grouped.

"23117-12" has three rows with three different dates.
I´d like them grouped.. starting with the oldest date and ASC.

So take the date that is oldest, and list the other result from that group.
Then you take the date from next dupe, list this group of dupes. and so on..

Do i need to explain my self better?

How is this possible?

EDIT

Expected output would be: enter image description here

Upvotes: 0

Views: 30

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

Simply select the minimum datum per objekt_element along in order to use it for sorting:

SELECT es.datum, es.objekt_element 
FROM element_skanning es
INNER JOIN 
(
  SELECT objekt_element, MIN(datum) AS min_datum
  FROM element_skanning
  GROUP BY objekt_element
  HAVING COUNT(*) > 1
) dup ON es.objekt_element = dup.objekt_element
ORDER BY dup.min_datum, es.objekt_element, es.datum;

Upvotes: 2

Related Questions