0

I have a table like this:

T              A    B    C   ID
2015-07-19     a    b    c   1
2015-07-16     a    y    z   2
2015-07-21     a    b    c   1
2015-07-17     a    y    c   2
2015-07-18     a    y    c   1
2015-07-20     a    b    c   1
2015-07-17     a    y    c   1
2015-07-19     a    b    c   2
2015-07-16     a    y    z   1
2015-07-20     a    b    c   2
2015-07-15     a    y    z   1
2015-07-22     x    b    c   1
2015-07-21     a    b    c   2
2015-07-18     a    y    c   2
2015-07-15     a    y    z   2
2015-07-22     a    y    c   2
2015-07-14     x    b    c   1

I need to get an ordered result by datetime column T, but I need that the query detects and avoid repeated rows in columns A, B and C. And all this ordered and separated by ID.

It could be a stored procedure. It's important to be fast, because is a huge log table. With millions of rows.

The result should be like this:

T              A    B    C   ID
2015-07-22     x    b    c   1
2015-07-19     a    b    c   1
2015-07-17     a    y    c   1
2015-07-15     a    y    z   1
2015-07-14     x    b    c   1
2015-07-22     a    y    c   2
2015-07-19     a    b    c   2
2015-07-17     a    y    c   2
2015-07-15     a    y    z   2

Any ideas?

rasputino
  • 691
  • 1
  • 8
  • 24
  • The table has like 20 not indexed columns, so I prefer to do not group by all this columns... if it's possible. – rasputino Jul 21 '15 at 09:35
  • How does the rest of your table look like? If this is it you could use `SELECT DISTINCT * FROM mytable ORDER BY id,T` – vinz Jul 21 '15 at 11:09
  • I want to detect the change between consecutive rows. So, in the example the rows `2015-07-22 x b c 1` and `2015-07-14 x b c 1` will be the same if the query is `SELECT A , B, C, ID FROM mytable ORDER BY id,T`. And all the rows will be different with `SELECT DISTINCT * FROM mytable ORDER BY id,T`because T is different... always. – rasputino Jul 21 '15 at 13:33
  • The table is an example, the real table is similar to the table proposed in [this solution](http://stackoverflow.com/questions/12563706/is-there-a-mysql-option-feature-to-track-history-of-changes-to-records) made by transient closure – rasputino Jul 21 '15 at 13:40

3 Answers3

1

Try this query:

SELECT max(t),a,b,c,id FROM table GROUP BY A,B,C,id ORDER BY ID, max(T)
Mukesh Kalgude
  • 4,814
  • 2
  • 17
  • 32
  • This doesn't not solve my problem. Because rows: 2015-07-22 x b c 1 2015-07-14 x b c 1 would be grouped in the same row. I need to get when a row changes in time... something like an historical log of changes. – rasputino Jul 21 '15 at 09:32
1

This query gives the expected result (tested):

SELECT t1.* FROM mytable t1 
LEFT  JOIN mytable t2 ON t1.t = t2.t + INTERVAL 1 DAY AND t1.A = t2.A AND t1.B = t2.B AND t1.C = t2.C AND t1.ID = t2.ID
WHERE t2.T IS NULL
ORDER BY t1.ID, t1.T DESC
vinz
  • 566
  • 3
  • 11
0

Try this query:

SELECT * FROM table GROUP BY A,B,C,T ORDER BY ID, T

Edit: added T to group by

vinz
  • 566
  • 3
  • 11
  • I'd replace `T` by `MAX(T)` to make sure to pick the latest ones. – Thomas Ruiz Jul 21 '15 at 09:04
  • This doesn't not solve my problem. Because rows: 2015-07-22 x b c 1 2015-07-14 x b c 1 would be grouped in the same row. I need to get when a row changes in time... something like an historical log of changes. – rasputino Jul 21 '15 at 09:32
  • With this query this is what you get: `2015-07-15 a y z 1 2015-07-16 a y z 1 2015-07-17 a y c 1 2015-07-18 a y c 1 2015-07-19 a b c 1 2015-07-20 a b c 1 2015-07-21 a b c 1 2015-07-22 x b c 1 2015-07-22 a y c 2` So your solution is wrong. I need to detect the change of the columns A,B,C. Not the change in the column T. And you are grouping the same id... i need to detect the change between IDs. Please, try your query before answer. Check my example. – rasputino Jul 21 '15 at 16:51