I have a table like this:
| A | B |
-----------------
| 22.1 | 15.8 |
| 12.15 | 4.55 | <- Duplicate record
| 12.15 | 4.55 | <- Duplicate record
| 12.15 | 4.55 | <- Duplicate record
| 30.4 | 44.12 |
| 10.5 | 7.58 |
| 31.2 | 65.1 | <- Duplicate record
| 31.2 | 65.1 | <- Duplicate record
| 9.4 | 7.8 |
| 12.15 | 4.55 | <- Same Duplicate record
| 12.15 | 4.55 | <- Same Duplicate record
| 12.15 | 4.55 | <- Same Duplicate record
| 31.2 | 65.1 | <- Same Duplicate record
| 31.2 | 65.1 | <- Same Duplicate record
Here I want to group A
and B
. But I want to group tandem values.
So if there is a different record between two same record then both record(same) should come.
For e.g. my expected output should be:
| A | B |
-----------------
| 22.1 | 15.8 |
| 12.15 | 4.55 | <- Group 1
| 30.4 | 44.12 |
| 10.5 | 7.58 |
| 31.2 | 65.1 | <- Group 2
| 9.4 | 7.8 |
| 12.15 | 4.55 | <- Group 3 (Second Time)
| 31.2 | 65.1 | <- Group 4 (Second Time)
What I am trying is:
SELECT * FROM MyTable
GROUP BY A,B
but it gives me wrong result:
| A | B |
-----------------
| 22.1 | 15.8 |
| 12.15 | 4.55 |
| 30.4 | 44.12 |
| 10.5 | 7.58 |
| 31.2 | 65.1 |
| 9.4 | 7.8 |
Here `12.15` and `31.2` is skipped second time. But I want it.
Note, I read this question but the solution is given in PHP while I want it in MySQL.
I am trying to solve the issue in this SQLFiddle.
Do you have a sequential ID on the column? If so, you can try something like:
SELECT t.A, t.B
FROM myTable t
WHERE NOT EXISTS
(
SELECT 1
FROM myTable t2
WHERE t2.A = t.A
AND t2.B = t.B
AND t2.Id = (SELECT MIN(t3.Id) FROM myTable t3 WHERE t3.Id > t.Id)
)
If you don't have an ID, you can try the following, but remember there is no guarantee that you will get the results you want; the server can return results in any order it wishes unless you specify a column to order by:
SELECT t.A, t.B
FROM
(
SELECT @curRow := @curRow + 1 AS Id, A, B
FROM myTable
JOIN (SELECT @curRow := 0) r ON 1=1
) t
WHERE NOT EXISTS
(
SELECT 1
FROM (
SELECT @curRow2 := @curRow2 + 1 AS Id, A, B
FROM myTable
JOIN (SELECT @curRow2 := 0) r ON 1=1
) t2
WHERE t2.A = t.A
AND t2.B = t.B
AND t2.Id = (
SELECT MIN(t3.Id)
FROM (
SELECT @curRow3 := @curRow3 + 1 AS Id, A, B
FROM myTable
JOIN (SELECT @curRow3 := 0) r ON 1=1
) t3
WHERE t3.Id > t.Id
)
)