CODEWITHSUNDEEP
mysqlsqldatabasedb2
Agrudge Amicus
Agrudge Amicus

Reputation: 1103

selecting data as groups from table

I have a table (Primary key as the combination of ID, COLUMN A and COLUMN B) with the following data:

 +---------+------------+------------+-----------+
 |   ID    |  COLUMN A  |  COLUMN B  |  COLUMN C |
 +---------+------------+------------+-----------+
 |   1     |    A1      |    B1      |    C1     |
 |   2     |    A2      |    B2      |    C2     |
 |   3     |    A1      |    B1      |    C3     |
 |   4     |    A3      |    B3      |    C1     |
 |   5     |    A1      |    B1      |    C3     |
 +-----------------------------------------------+

I want data fetched as:

 +---------+------------+------------+-----------+
 |   ID    |  COLUMN A  |  COLUMN B  |  COLUMN C |
 +---------+------------+------------+-----------+
 |   2     |    A2      |    B2      |    C2     |
 |   4     |    A3      |    B3      |    C1     |
 |   5     |    A1      |    B1      |    C3     |
 +-----------------------------------------------+

Should I be using this:

  SELECT * FROM TABLE_NAME
  GROUP BY 
  COLUMN A, COLUMN B
  ORDER BY ID;

Upvotes: 0

Views: 38

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

Here is way to do this without using window functions, assuming you might be using an older version of MySQL:

SELECT t1.ID, t1.A, t1.B, t1.C
FROM yourTable t1
INNER JOIN
(
    SELECT A, B, MAX(ID) AS MAX_ID
    FROM yourTable
    GROUP BY A, B
) t2
    ON t1.A = t2.A AND t1.B = t2.B AND t1.ID = t2.MAX_ID
ORDER BY
    t1.ID;

Upvotes: 2

zealous
zealous

Reputation: 7503

Without using window function where you can use max. here is the demo.

select
    max(ID) as ID,
    columnA,
    columnB,
    max(columnC) as columnC
from myTable
group by    
    columnA,
    columnB
order by    
    ID

You can use window function row_number, Here is the demo.

select
  ID,
  columnA,
  columnB,
  columnC
from
(
  select
    *,
    row_number() over (partition by columnA, columnB order by ID desc) as rn
  from yourTable
) subq
where rn = 1

output:

| ID  | columnA | columnB | columnC |
| --- | ------- | ------- | ------- |
| 2   | A2      | B2      | C2      |
| 4   | A3      | B3      | C1      |
| 5   | A1      | B1      | C3      |

Upvotes: 1

Related Questions