Find the least-appearing numbers in the records

Question

Personal greetings. I have a sort of table 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 find out what the latest numbers are, in order. E.g:

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

I’ve tried a few ways but to no avail. Could someone give me an idea?

Thank you!

Answer 1

I will divide logic into separate steps to facilitate.

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

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

Once obtained this record, just count how many there are after it. In your example, we would have received 3 in return.

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

SELECT COUNT(*) AS atrasado WHERE reg > 3

If you are using any auxiliary support language, this can be done with two darlings and use of variables.

Putting the two things together

We can make a subquery to obtain 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 will constantly use this feature

You can create a FUNCTION which is part of the database, rather than using the 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 as that):

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

See working on SQL Fiddle.