0

I'm doing the leetcode SQL problems and kind of confused with the code below:

select distinct Num from (
    select
        Num,
        case
            when @prevNum = Num then @count := @count + 1
            when (@prevNum := Num) is not null then @count := 1
        end n
    from Logs, (select @prevNum := NULL) r
    order by Id
) a where n >= 3

Two questions:

First when (@prevNum := Num) is not null what is the logic value of assignment expression in SQL? I have googled MySQL and Oracle reference book, this forum and I got nothing about it. And this condition is really confusing.

Second the n after case end statement, I know n stands for the alias of case statement, but there are two variables in case end statement here, how do SQL know that n stands for @count but not @prevNum?

Thanks

The question is as follows:

Write a SQL query to find all numbers that appear at least three times consecutively.

+----+-----+
| Id | Num |
+----+-----+
| 1  |  1  |
| 2  |  1  |
| 3  |  1  |
| 4  |  2  |
| 5  |  1  |
| 6  |  2  |
| 7  |  2  |
+----+-----+

For example, given the above Logs table, 1 is the only number that appears consecutively for at least three times.

2
  • Which DBMS are you using? @prevNum = .. is non-standard SQL (i.e. invalid for most DBMS). And what exactly is the "leetcode SQL problem"?
    – user1822
    May 3, 2015 at 22:02
  • I think they are using MySQL because they support limit but not top, and I have updated the question.
    – Tony Chen
    May 3, 2015 at 22:09

1 Answer 1

1

PROPOSED SOLUTION

Using the IF() function to check for consecutive num values

SET @x = 0;
SET @n = 0;
SELECT num,consecutive FROM
(SELECT
    id,num,
    (@x:=IF(num=@n,@x+1,1)) consecutive,
    (@n:=num) inc
FROM logs) A
WHERE consecutive >= 3;

YOUR SAMPLE DATA

DROP DATABASE IF EXISTS tonychen;
CREATE DATABASE tonychen;
USE tonychen
CREATE TABLE logs
(
  id INT NOT NULL AUTO_INCREMENT,
  num INT NOT NULL,
  PRIMARY KEY (id)
);
INSERT INTO logs (num) VALUES (1),(1),(1),(2),(1),(2),(2);
SELECT * FROM logs;

YOUR SAMPLE DATA LOADED

mysql> DROP DATABASE IF EXISTS tonychen;
Query OK, 1 row affected (0.25 sec)

mysql> CREATE DATABASE tonychen;
Query OK, 1 row affected (0.00 sec)

mysql> USE tonychen
Database changed
mysql> CREATE TABLE logs
    -> (
    ->   id INT NOT NULL AUTO_INCREMENT,
    ->   num INT NOT NULL,
    ->   PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.31 sec)

mysql> INSERT INTO logs (num) VALUES (1),(1),(1),(2),(1),(2),(2);
Query OK, 7 rows affected (0.05 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM logs;
+----+-----+
| id | num |
+----+-----+
|  1 |   1 |
|  2 |   1 |
|  3 |   1 |
|  4 |   2 |
|  5 |   1 |
|  6 |   2 |
|  7 |   2 |
+----+-----+
7 rows in set (0.00 sec)

mysql>

PROPOSED SOLUTION EXECUTED

mysql> SET @x = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @n = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT num,consecutive FROM
    -> (SELECT
    ->     id,num,
    ->     (@x:=IF(num=@n,@x+1,1)) consecutive,
    ->     (@n:=num) inc
    -> FROM logs) A
    -> WHERE consecutive >= 3;
+-----+-------------+
| num | consecutive |
+-----+-------------+
|   1 |           3 |
+-----+-------------+
1 row in set (0.00 sec)

mysql>

GIVE IT A TRY !!!

1
  • Thank you for working it out so quickly. I have tried that on my MySQL and it perfectly works. However, I do know that there are other answers and I'm just confused about the code I posted, which is specifically about the case... when... then clause. Can you do me a favor and answer that?
    – Tony Chen
    May 4, 2015 at 0:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.