CODEWITHSUNDEEP
mysqlsqlmysql-workbench
Md. Parvez Alam
Md. Parvez Alam

Reputation: 4596

Mysql multiple max values from a table

I have below structure

+----+----+
| C1 | C2 |
+---------+
| 1  | A  |
| 2  | A  |
| 3  | A  |
| 4  | B  |
| 5  | B  |
| 6  | A  |
| 7  | C  |
+----+----+

Output should be

C1  C2    
3    A    
5    B    
6    A    
7    C

It should return the highest C1 value in each sequence that has the same value of C2

The simple group is not working, please guide how to achieve this.

Thanks in advance

Upvotes: 0

Views: 68

Answers (3)

forpas
forpas

Reputation: 164154

You want the max c1 for each series of c2:

select t.* from tablename t
where 
  coalesce(
    (select c2 from tablename where c1 = (
      select min(c1) from tablename where c1 > t.c1)
    ), ''
  ) <> t.c2

See the demo.
Results:

| C1  | C2  |
| --- | --- |
| 3   | A   |
| 5   | B   |
| 6   | A   |
| 7   | C   |

Upvotes: 2

Nick
Nick

Reputation: 147206

You can do this by LEFT JOINing the table to itself on the next value in the table having a different C2 value. We use a GROUP BY to get the last row in the table which has no match:

SELECT MAX(d1.C1) AS C1, MAX(d1.C2) AS C2
FROM data d1
LEFT JOIN data d2 ON d2.C2 != d1.C2
                 AND d2.C1 = (SELECT MIN(C1) FROM data d3 WHERE d3.C1 > d1.C1)
GROUP BY d2.C1
ORDER BY C1

Output:

C1  C2
3   A
5   B
6   A
7   C

Demo on dbfiddle

Upvotes: 2

sticky bit
sticky bit

Reputation: 37482

Seems like you want records where no higher c1 exists or the c2 of the next higher c1 is not equal to the current c2. You can get the next c2 in a subquery ordering by c1 using LIMIT 1.

SELECT t1.c1,
       t1.c2
       FROM elbat t1
       WHERE NOT EXISTS (SELECT t2.c2
                                FROM elbat t2
                                WHERE t2.c1 > t1.c1)
              OR (SELECT t2.c2
                         FROM elbat t2
                         WHERE t2.c1 > t1.c1
                         ORDER BY t2.c1
                         LIMIT 1) <> t1.c2;

db<>fiddle

Upvotes: 3

Related Questions