Форум пользователей MySQL

mysql> create table t_3839(id int, login varchar(255),`time` int);
Query OK, 0 rows affected (0.31 sec)

mysql> insert into t_3839 values(1,'log1',10),(2,'log1',15),(3,'log2',100),(4,'log2',80),(5,'log3',11),(6,'log4',8);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> set @i=0,@n=1,@l=''; select t.* from ( select * from t_3839 order by login, time desc) t where if (@l=login, @i:=@i+1,(@i:=0) or (@l:=login) or 1) and @i<@n;
Query OK, 0 rows affected (0.02 sec)

+------+-------+------+
| id   | login | time |
+------+-------+------+
|    2 | log1  |   15 |
|    3 | log2  |  100 |
|    5 | log3  |   11 |
|    6 | log4  |    8 |
+------+-------+------+
4 rows in set (0.04 sec)
mysql> select version();
+------------+
| version()  |
+------------+
| 5.1.52-log |
+------------+
1 row in set (0.02 sec)

MariaDB [sqlinfo]> create table t_3839(id int, login varchar(255),`time` int);
Query OK, 0 rows affected (0.23 sec)

MariaDB [sqlinfo]> insert into t_3839 values(1,'log1',10),(2,'log1',15),(3,'log2
',100),(4,'log2',80),(5,'log3',11),(6,'log4',8);
Query OK, 6 rows affected (0.28 sec)
Records: 6  Duplicates: 0  Warnings: 0

MariaDB [sqlinfo]>   set @i=0,@n=1,@l=''; select t.* from ( select * from t_3839 order by login, time desc) t where if (@l=login, @i:=@i+1,(@i:=0) or (@l:=login) or 1) and @i<@n;
Query OK, 0 rows affected (0.00 sec)

+------+-------+------+
| id   | login | time |
+------+-------+------+
|    1 | log1  |   10 |
+------+-------+------+
1 row in set (0.00 sec)

MariaDB [sqlinfo]> select version();
+--------------------+
| version()          |
+--------------------+
| 5.5.34-MariaDB-log |
+--------------------+
1 row in set (0.01 sec)

MariaDB [sqlinfo]> set @n=3, @i=0, @p=0; select * from (select * from g t1 join
p t2 on t1.x=t2.y order by t1.x) t WHERE if (@p=x, @i:=@i+1,(@i:=0) or (@p:=x))
and @i<@n;
Query OK, 0 rows affected (0.02 sec)

+------+------+------+
| x    | y    | data |
+------+------+------+
|    3 |    3 |  172 |
|    3 |    3 |  721 |
|    3 |    3 |  652 |
+------+------+------+
3 rows in set (0.05 sec)

MariaDB [sqlinfo]>

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 30
Server version: 5.5.34-MariaDB-log mariadb.org binary distribution

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use sqlinfo;
Database changed
MariaDB [sqlinfo]> SELECT * FROM t_3839 t;
+------+-------+------+
| id   | login | time |
+------+-------+------+
|    1 | log1  |   10 |
|    2 | log1  |   15 |
|    3 | log2  |  100 |
|    4 | log2  |   80 |
|    5 | log3  |   11 |
|    6 | log4  |    8 |
+------+-------+------+
6 rows in set (0.00 sec)

MariaDB [sqlinfo]> select t.* from ( select * from t_3839, (select @i:=0,@n:=1,@
l:='') x order by login, time desc) t where if (@l=login, @i:=@i+1,(@i:=0) or (@
l:=login) or 1) and @i<@n;
+------+-------+------+-------+-------+--------+
| id   | login | time | @i:=0 | @n:=1 | @l:='' |
+------+-------+------+-------+-------+--------+
|    2 | log1  |   15 |     0 |     1 |        |
+------+-------+------+-------+-------+--------+
1 row in set (0.02 sec)

MariaDB [sqlinfo]> select version();
+--------------------+
| version()          |
+--------------------+
| 5.5.34-MariaDB-log |
+--------------------+
1 row in set (0.23 sec)

MariaDB [sqlinfo]>