Mysql – Get previous row and compare values

MySQL

I need to compare values of neighbouring rows in a table and get the number of times the value 42 of column calledstationid precedes the value 34. This is a system log table and calledstationid is not an auto-increment column.

I created this query but it has some problems:

SELECT COUNT(*) FROM (

    SELECT
        h.acctstarttime, h.acctstoptime, h.radacctid, h.calledstationid,
        (
            SELECT MAX(sub.calledstationid) FROM radius.radacct_historical as sub 
            WHERE sub.callingstationid = '54:9F:13:27:31:7F'
            AND sub.acctstarttime < h.acctstarttime
        ) as previous,

    h.callingstationid

    CASE WHEN h.calledstationid = 34 AND previous = 42 THEN 'yes' END AS goal

    FROM radius.radacct_historical  AS h 
    WHERE h.callingstationid = '54:9F:13:27:31:7F'
    ORDER BY h.acctstarttime ASC;

) as total WHERE goal = 'yes'

First problem:

(
    SELECT MAX(sub.calledstationid) FROM radius.radacct_historical as sub 
    WHERE sub.callingstationid = '54:9F:13:27:31:7F'
    AND sub.acctstarttime < h.acctstarttime
) as previous,

The previous calledstationid is not returned properly. I get the correct value only in the first 2 results, thereafter it seems that MySQL stores the value of the result in memory and does not update the subquery.

Second problem:

CASE WHEN h.calledstationid = 34 AND previous = 42 THEN 'yes' END AS goal

The column name previous in the CASE expression is not recognised.

Could anyone shed any light on how to do this?

http://www.sqlfiddle.com/#!2/774ff/42/1

Best Answer

You have replaced the previous column reference in the CASE expression with the same subquery that you are using earlier to calculate the value of previous. While that certainly resolves the issue of referencing a calculated column at the same nesting level, duplication of code is far from an ideal solution.

You can avoid the duplication by abandoning the CASE expression completely as unnecessary. All it does is producing a "yes"/"no" result based on the values of the current and previous calledstationid only to be compared to "yes" at the outer level. Instead of checking on the value of achou, your outer level can be checking on the values of calledstationid and previous directly, like this:

SELECT COUNT(achou) as total
FROM (
    SELECT
        h.calledstationid,
        (
            SELECT calledstationid
            FROM radius.radacct_historical as sub 
            WHERE sub.callingstationid = '54:9F:13:27:31:7F'
              AND sub.acctstarttime < h.acctstarttime
            ORDER BY sub.acctstarttime DESC
            LIMIT 1 
        ) as previous,
    FROM radius.radacct_historical  AS h 
    WHERE h.callingstationid = '54:9F:13:27:31:7F'
) as S WHERE S.calledstationid = 34 AND S.previous = 42;

As you can see, I have also removed other elements from the query, like most of the columns returned by the nested SELECT (acctstarttime, acctstoptime, radacctid, callingstationid – they neither participate in the output nor affect the count result) and the nested ORDER BY h.acctstarttime ASC (there is no need to sort rows before counting them).

Related Solutions

MySQL – Using Index for Both ASC and DESC on String Column

Although the syntax for CREATE INDEX supports an option for defining the index as ascending or descending, in the most common two storage engines (InnoDB and MyISAM), this index option is a no-op. There is no such thing as an "ascending" or "descending" index, they are just indexes and can be used for sorting in either direction.

But in the case you describe, where you have a multi-column index and you want to order by one column ascending and the other column descending, that's a different story. The sort order must go in the same direction for both columns if it uses an index.

(Not that every sort has to use an index. You can certainly run the query and let it sort matching rows with a filesort.)

To reverse the sort order of strings, you could add your own custom collation. https://dev.mysql.com/doc/refman/5.6/en/adding-collation.html

But you'd have to store your strings using that new collation, and rebuild your index. The index would only be useful for the reverse-sorting.

Re comment from @Cratylus:

A collation is what defines "A" as being before "B" and all the other ordering of letters. So if you define a collation that says that "B" is before "A", and use that collation in your column definition, then sorting on that column, or creating an index on the column, would be in the reverse order.

Note: the following doesn't create a proper collation, it just demonstrates the steps to create one.

Edit /usr/share/mysql/charsets/Index.xml and make this edit:

<charset name="latin1">

  . . . leave other collations alone . . .

  <collation name="latin1_reverse_ci" id="251">
    <order>Dutch</order>
    <order>English</order>
    <order>French</order>
    <order>German Duden</order>
    <order>Italian</order>
    <order>Latin</order>
    <order>Portuguese</order>
    <order>Spanish</order>
  </collation>

</charset>

Edit /usr/share/mysql/charsets/latin1.xml and make this edit:

<charset name="latin1">

. . . leave other collations alone . . .

<collation name="latin1_reverse_ci">
<map>
 AE B1 AC A2 A0 9E 9C 8D BE 8B 89 87 85 83 7F 59
 73 71 6F 6D 63 61 5F 5D 55 4F 4D 4B 49 47 45 43
 97 B1 AC A2 A0 9E 9C 8D BD 8B 89 87 85 83 7F 59
 73 71 6F 6D 63 61 5F 5D 55 4F 4D 4B 49 47 45 43
 FF FE FD FC FB FA F9 F8 F7 F6 F5 F4 F3 F2 F1 F0
 EF EE ED EC EB EA E9 E8 E7 E6 E5 E4 E3 E2 E1 E0
 DF DE DD DC DB DA D9 D8 D7 D6 D5 D4 D3 D2 D1 D0
 CF CE CD CC CB CA C9 C8 C7 C6 C5 C4 C3 C2 C1 C0
 BF BC BB BA B9 AF AA A8 A6 A4 9A 98 95 93 91 8F
 81 7D 7B 79 77 75 6B 69 67 65 5B 57 53 51 41 B8
 B7 B6 B5 B4 B3 AF AA A8 A6 A4 9A 98 95 93 91 8F
 81 7D 7B 79 77 75 6B 69 67 65 5B 57 53 51 41 40
 3F 3E 3D 3C 3B 3A 39 38 37 36 35 34 33 32 31 30
 2F 2E 2D 2C 2B 2A 29 28 27 26 25 24 23 22 21 20
 1F 1E 1D 1C 1B 1A 19 18 17 16 15 14 13 12 11 10
 0F 0E 0D 0C 0B 0A 09 08 07 06 05 04 03 02 01 00
</map>
</collation>

</charset>
. . .

Restart mysqld.

Confirm you have the new collation:

mysql> SHOW COLLATION LIKE 'latin1%';
+---------------------+---------+-----+---------+----------+---------+
| Collation           | Charset | Id  | Default | Compiled | Sortlen |
+---------------------+---------+-----+---------+----------+---------+
. . .
| latin1_general_ci   | latin1  |  48 |         | Yes      |       1 |
| latin1_general_cs   | latin1  |  49 |         | Yes      |       1 |
| latin1_spanish_ci   | latin1  |  94 |         | Yes      |       1 |
| latin1_reverse_ci   | latin1  | 251 |         |          |       0 |
. . .

Create a table and fill it with some text:

mysql> CREATE TABLE foo (
    id INT AUTO_INCREMENT PRIMARY KEY,
    t VARCHAR(60) COLLATE latin1_reverse_ci, 
    x DATE,
    KEY(t)
);

mysql> INSERT INTO foo (t) VALUES ('Harry'), ('Ron'), ('Hermione');

Demonstrate that the sort order is the reverse of what you'd think it should be:

mysql> SELECT * FROM foo ORDER BY t ASC;
+----+----------+------+
| id | t        | x    |
+----+----------+------+
|  2 | Ron      | NULL |
|  3 | Hermione | NULL |
|  1 | Harry    | NULL |
+----+----------+------+

Confirm that it doesn't use a filesort:

mysql> EXPLAIN SELECT * FROM foo WHERE t LIKE 'H%' ORDER BY t\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: foo
   partitions: NULL
         type: range
possible_keys: t
          key: t
      key_len: 63
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using index condition

I had to put a condition on the rows, because otherwise it thought it was going to read all the rows in the table, and it did a table-scan and filesort.

Re comment from @ypercube:

It doesn't reverse the order of characters in a given string like REVERSE(), it reverses which characters are considered greater than or less than each other when comparing two strings. So no, the CHAR padding spaces don't matter, they're still at the end of both strings.

Example (no index this time, just using order by):

mysql> create table t (c char(20) collate latin1_reverse_ci);
mysql> insert into t values ('A'), ('B'), ('C');
mysql> select * from t order by c;
+------+
| c    |
+------+
| C    |
| B    |
| A    |
+------+

Edit: no, @ypercube is correct, this doesn't work with CHAR because the space character is in the wrong place when I naively reorder the collation simply by putting all the bytes in the reverse order. That's not the proper way to design a collation.

So this answer should serve as a demo of the mechanics of creating a collation, not as a proper collation for reversing alphabetical order.

MySQL – How to Get Previous and Next Row from Result Set

Instead of 1424, perhaps you should use t4.t2id. Instead of 1046, if the parent exists in table4 or any of the others, use that link instead of the constant.

You could also use a single IN, and a UNION of both queries.

Finally, if you use a filter condition in WHERE on a LEFT JOINed table, that defeats the purpose of a LEFT JOIN and might result in bugs. Either use JOIN or move the condition to the ON part of that LEFT JOIN. In this case, the condition in the LEFT JOIN was different than the one in WHERE for the same field (t2.id), so it could never work.

SELECT 
CONCAT_WS('/', LOWER(t1.name), LOWER(t2.slug), LOWER(t3.slug)) as slug,
t1.id, t2.id, t3.id
FROM table4 t4
left join table1 t1 on t1.id = t4.t1id
left join table2 t2 on t2.id IN (
    SELECT COALESCE((SELECT MAX(id) FROM table2 WHERE parent = 1046 AND id < t4.t2id),
                    (SELECT MAX(id) FROM table2 WHERE parent = 1046))
    UNION
    SELECT COALESCE((SELECT MIN(id) FROM table2 WHERE parent = 1046 AND id > t4.t2id),
                    (SELECT MIN(id) FROM table2 WHERE parent = 1046)))
left join table3 t3 on t3.id = t4.t3id
where t4.t2id = 1424

Best Answer

Related Solutions

MySQL – Using Index for Both ASC and DESC on String Column

MySQL – How to Get Previous and Next Row from Result Set

Related Question