Is there a way to start a Select (SQL) in the middle of the data table?

Question

Is there a way to start a Select (SQL) in the middle of the data table?

Navigation

#1 by (3 votes)
#2 by (2 votes)
#3 by (0 votes)

3

Is there any way in the SQL language (and using BD mysql) where I can start a Select in the middle of a table?

For example, I have 500 addresses in the table. I search the addresses of Bairro X (where neighborhood="x"), reducing the total to, say, 60 addresses. But I wanted to start listing from half of those 60 onwards, ignoring the previous ones (even though they are from the neighborhood "x"). How?

Another issue is that I do not know how many values I will have depending on the neighborhood selected, so it may vary. 60, 68, 90, etc. Is there a function inside SQL that makes the select list start in the middle (or at any point, for example, 1/3, 2/3, 5/6 etc) of a select + where result?

Thank you!

mysql sql sql-select select

asked by anonymous 19.12.2017 / 16:35

3 answers

How to link tables that are in different databases? string transformation in R

score 3 · Answer 1

Using LIMIT and OFFSET

Show 10 records beginning with the first one:

SELECT * FROM TABELA LIMIT 0, 10

Show 10 records starting from the tenth:

SELECT * FROM TABELA LIMIT 10, 10

Show 32 records starting at 45 °:

SELECT * FROM TABELA LIMIT 45, 32

score 2 · Answer 2

You can index the results in ascending order and then take only the records that are greater than half the total of the records found:

SET @i := 0;
SELECT * FROM (
  SELECT *, @i := @i + 1
  AS num
  FROM tabela
  ORDER BY bairro, id
) AS t
WHERE num > (
  SELECT
  COUNT(produto) - (COUNT(produto) / 2)
  FROM tabela
  WHERE bairro = 'x'
)
AND bairro = 'x';

Where id is the primary index and x is the "neighborhood" code.

For example:

If 6 records are found, WHERE num > will only get the records with num greater than half, that is, from 4 to 6 (3 records).

----------------------
| id | bairro  | num |
----------------------
| 11 | bairroX |  1  |
----------------------
| 14 | bairroY |  2  |
----------------------
| 18 | bairroX |  3  |
----------------------
| 21 | bairroZ |  4  | ←
----------------------
| 35 | bairroA |  5  | ←
----------------------
| 39 | bairroF |  6  | ←
----------------------

If you want to get 1/3 part of the result, just change the 2 in COUNT(produto) - (COUNT(produto) / 2) to 3 ; the fourth part, 4 , and so on.

score 0 · Answer 3

When the procedure is something more complex, we can work with procedure so you do not have to create variáveis globais and end up not returning the expected result, so we will have a complete execution of the statement block created in PROCEDURE . In this procedure let's set the value of the variable to start in the middle of the records of the table and take it to the end of it (that is the total number of records) to test only change the tag [tabela] and create procedure

DROP PROCEDURE IF EXISTS sp_get_bairros;
DELIMITER |
CREATE PROCEDURE sp_get_bairros()
BEGIN

    DECLARE v_inicio INT;
    DECLARE v_fim INT;

    SELECT
            COUNT(*)
    INTO v_fim
    FROM [tabela];

    SET v_inicio = v_fim / 2;

    SELECT
        *
    FROM [tabela]
    LIMIT v_inicio, v_fim;


END
|
DELIMITER ;

Soon after creation, just call CALL sp_get_bairros()

Ref: link