SQL query with mariadb in which the first 3 rows will be shown and the last row added with their position to make a classification

Question

SQL query with mariadb in which the first 3 rows will be shown and the last row added with their position to make a classification

Navigation

#1 by (1 votes)
#2 by (1 votes)

0

I'm doing a classification for a game, to do the classification I'm using the following query with MariaDB 10.1.21 :

SELECT @rownum:=@rownum+1 'posicion',
       nombre, 
       dificultad as fallos,
       palabra,
       fecha,
       id 
FROM palabras p , 
(SELECT @rownum:=0) r 
where nombre IS NOT NULL 
ORDER BY dificultad, fecha DESC LIMIT 3

This query tells you the records by difficulty and date, it also gives you the position in the ranking but it turns out that if the player is not among the first three his position is not shown, as is normal .

I am looking for a query that returns the first 3 and the last inserted with its position, the id of the last row inserted I have it saved in php in the variable $ ultimo

example:

posición nombre dificultad palabra    fecha      id

1          pepe     0      morsa      2018-01-04 1
2          pepe     1      hola       2018-12-30 2
3          pepe     4      niño       2018-12-31 3
7          pablo    7      paragüero  2019-01-04 4

Pablo would be the last player as he failed 7 times would be below the first 3 and I would like him to show it with his position

sql mariadb

asked by javier 04.01.2019 в 14:17

source

2 answers

1

A quick solution could be that you use a UNION to join the two queries, the one you already have that shows you the first 3 and a new one that shows you only the last record inserted. something like this:

(SELECT @rownum:=@rownum+1 'posicion',
   nombre, 
   dificultad as fallos,
   palabra,
   fecha,
   id 
 FROM palabras p , 
 (SELECT @rownum:=0) r 
 where nombre IS NOT NULL 
 ORDER BY dificultad, fecha DESC LIMIT 3 ) 
UNION 
(SELECT @rownum:=@rownum+1 'posicion',
   nombre, 
   dificultad as fallos,
   palabra,
   fecha,
   id 
 FROM palabras order by id desc limit 1)

You may have to modify the second query to your need.

answered by 04.01.2019 в 15:20

Problem with border-image Join 2 mysql queries to get a common result

score 1 · Accepted Answer

The solution that occurs to me is to work with a temporary table, to persist the position first:

CREATE TEMPORARY TABLE T AS (
    SELECT  @rownum := @rownum + 1 'posicion',
            nombre, 
            dificultad as fallos,
            palabra,
            fecha,
            id 
            FROM palabras p,
            (SELECT @rownum := 0) r
            where nombre IS NOT NULL
            ORDER BY dificultad, fecha DESC 
);

And now if, taking the position, we can make the query you are looking for:

The first three
The last inserted

And now the final query:

SELECT  *
        FROM T
        WHERE posicion <= 3
              OR id = (SELECT MAX(id) FROM T)
        ORDER BY posicion