Group tandem records

Question

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.

@Downvoter, Is there any issue from my efforts? – aditya Nov 09 '12 at 04:41 — aditya, Nov 09 '12 at 04:41

lc. · Accepted Answer · 2012-11-09T04:48:07.683

2

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)
)

SQL Fiddle example

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
   )
)

SQL Fiddle example

edited Nov 09 '12 at 04:48

answered Nov 09 '12 at 04:36

lc.

113,939
20
158
187

Ahh, That's the main problem that I don't have other column like `ID`. – aditya Nov 09 '12 at 04:38
@aditya No timestamp either? In that case there's really no way to *guarantee* an ordering on the results. If the server wanted to, it can return the rows in any order it wants; there is no contract saying it must give you the rows in the order specified. – lc. Nov 09 '12 at 04:43
Can't we add something like `Row_number` while selecting a value? – aditya Nov 09 '12 at 04:45
@aditya Yes and that's what I did, check my edit. The thing is it is not *100% guaranteed* the row number will be the same in every execution of the query, so the best thing would be to add an id or timestamp to the table. – lc. Nov 09 '12 at 04:50
That's great. Just for note if we don't want to order we can use `ORDER BY NULL` [SQLFiddle](http://sqlfiddle.com/#!2/11de1/11) – aditya Nov 09 '12 at 04:53
1

Yes, except you'll run into the same problem. Your question suggests you want an order (you want to remove duplicate records *that appear next to each other*) but not specifying an order (or the equivalent of specifying ORDER BY a constant) does not exactly guarantee you'll have the correct records next to each other. That's all I'm saying. – lc. Nov 09 '12 at 05:11
Okay thanks. I will add a column something like `ID`. So that I can get my desired result with guarantee. :) (using your first solution) – aditya Nov 09 '12 at 05:14

Group tandem records

1 Answers1