Discover the numbers that least appear in the records

Question

Discover the numbers that least appear in the records

Navigation

#1 by (6 votes)

3

Personal greetings. I have a table something like this:

reg  num0 num1 num2 num3 num4 num4
1     4    2    5    8    10   15   
2     3    7    8    15   20   21
3     14   10   6    21   17   1
4     1    4    8    10   16   7
5     9    3    2    1    8    15

I need to figure out which numbers are backward, in order. E.g:

Number 21 - > delayed to 3 records (because the last time it appeared it was in record number 2).
Number 5 - > delayed to 5 records (because the last time it appeared it was in record number 1).

I have tried in some ways but to no avail. Could someone give me an idea?

Thank you!

mysql sql

asked by anonymous 12.11.2015 / 04:24

1 answer

Regular expression to extract numbers from "200 # 5; 300 # 10" How to use Javascript to manipulate data from the database? [closed]

score 6 · Accepted Answer

I'll split the logic into separate steps to make it easier.

First, let's see where the last occurrence of number 21 was, for example:

SELECT    reg
FROM      tabelaMaisOuMenosAssim
WHERE     21 IN (num0,num1,num2,num3,num4,num5)
ORDER BY  reg DESC
LIMIT     1;

Once you get this record, just count how many there are after it. In your example, we would have received 3 as a return.

To know how many came after (you can use >= if you want to include the current line):

SELECT COUNT(*) AS atrasado WHERE reg > 3

If you are using some support language, this can be done with two queries and use of variables.

Putting the two together

We can do a subquery to get the result in a single operation:

SELECT COUNT(*) AS atrasado
FROM   tabelaMaisOuMenosAssim
WHERE  reg >= (
   SELECT    reg
   FROM      tabelaMaisOuMenosAssim
   WHERE     21 IN (num0,num1,num2,num3,num4,num5)
   ORDER BY  reg DESC
   LIMIT     1
);

See working on SQL Fiddle .

If you are going to use this feature constantly

You can create a FUNCTION that is part of the database instead of using the entire query every time you need to get a value:

CREATE FUNCTION pegaAtrasado(numero int)
RETURNS int
RETURN (
  SELECT COUNT(*)
  FROM   tabelaMaisOuMenosAssim
  WHERE  reg >= (
    SELECT    reg
    FROM      tabelaMaisOuMenosAssim
    WHERE     numero IN (num0,num1,num2,num3,num4,num5)
    ORDER BY  reg DESC
    LIMIT     1
  )
);

How to use (simple like this):

SELECT pegaAtrasado( 21 );
SELECT pegaAtrasado(  5 );
SELECT pegaAtrasado(  1 );

See working on SQL Fiddle .