Can you start a Select (SQL) in the middle of the data table?

Asked

Viewed 924 times

3

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

For example, I have 500 addresses in the table. I do a search of Neighborhood X addresses (Where neighborhood="x"), reducing the total to, say, 60 addresses. But I wanted to start the listing from half of those 60 onwards, ignoring the previous ones (even if they were also from the "x" neighborhood). It has as?

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

Thank you very much!

mysql sql select select-sql

  • Which columns are in this table?

  • has , use having com Count... https://www.w3schools.com/sql/sql_having.asp

  • Actually there are several columns. I put this example of addresses just to facilitate the explanation of what I’m looking for. It got weird wanting to ignore some addresses ne? hahaha It’s a table with hints. So depending on the variables of Where, the person would receive some tips. But I don’t want to put Rand().

  • Something like, for instance. "Select * from tips Where age>50 AND Sex=1.

  • Marconcilio... now I understand your suggestion better! I’ll try! Thank you!!!

  • The having Count didn’t help. In the example I quoted in my comment above, for example, I don’t know where I could put it to start from some point in the listing.

Show 1 more comment

3 answers

3

Using LIMIT and OFFSET

Show 10 records starting from the first:

SELECT * FROM TABELA LIMIT 0, 10

Show 10 records starting from tenth:

SELECT * FROM TABELA LIMIT 10, 10

Show 32 records starting from 45°:

SELECT * FROM TABELA LIMIT 45, 32

  • Not a good option: LIMIT 10000, 20 means it will read 100020 and throw away 10000 to return 20 lines.

  • I’ll test it. Maybe it works. I already use Limit 10 pq and I want to show only 10 results. The challenge there would be to get to know the total that Where will return me, so set from where this Limit 10 will start the search. But I think it’s closer than I look for. If it doesn’t work out, I’ll see if I can fix it in php. I take all the listing that comes from the Where condition and then via loop in php I start showing only from half of the total.

  • This is the best option if you want to make paging (it seems to me). To know the total of records do a SELECT COUNT(1) FROM TABLE, then you can via code break down the number of pages depending on the number of records per page.

2

You can index the results in ascending order and then take only the records that are greater than half of the total 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 code of "neighborhood".

For example:

If 6 records are found, the WHERE num > will pick up only 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 the 1/3 part of the result, just change the 2 in COUNT(produto) - (COUNT(produto) / 2) for 3; the fourth part, 4, and so on.

0

When the procedure is something more complex, we can work with procedure so you don’t have to keep creating variáveis globais and end up not returning the expected result, so we will have a complete execution of the instruction block created in the PROCEDURE. In that procedure We set the value of the variavei start at half of the table records and take until the end of it (which is the total amount of records) to test only change the tag [tabela] and create the 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 ;

Right after creation, just call as follows CALL sp_get_bairros()

Ref: https://dev.mysql.com/doc/refman/5.7/en/create-procedure.html

Browser other questions tagged mysql sql select select-sql

You are not signed in. Login or sign up in order to post.