SELECT filtering the first results

Asked

Viewed 163 times

2

I have the HIGHLIGHT field (yes) (no) I would like to display the results by removing the first 4 results HIGHLIGHT (yes) and display the rest of the records both (yes) from the 4th and (no)

I tried to use LIMIT OFFSET but it didn’t work

SELECT * FROM noticias WHERE status='Publicado' ORDER BY idnoticia DESC LIMIT 6,4

some alternative?

php sql
  • 2

    Post what tried and didn’t work. LIMIT OFFSET is the most appropriate. Which bank is using? Also add an example, to make it clearer.

  • You are using mysql?

3 answers

2

Use the operation UNION to unite the result of two different consultations into one.

The consultation before the UNION would have only the results highlighted, while the subsequent consultation would be supplemented with the results not highlighted.

SELECT *
FROM noticias
WHERE status='Publicado'
AND destacar='sim'
ORDER BY idnoticia DESC LIMIT 4
UNION
SELECT *
FROM noticias
WHERE status='Publicado'
AND destacar='nao'
ORDER BY idnoticia DESC

From then on just implement a pagination in your own way, using LIMIT and OFFSET.

  • 1

    But in this case the 2nd query will not display the destacar='sim' that are below the fourth position, right? It would not be the case for the 2nd query to display all records except the ID’s that are listed in the 1st query?

  • 1

    @Papacharlie by what I understood the rows of the table have 'yes' or not' in the column, which means that there would be no repetition between the results of both queries. Depends a little on the table structure too.

  • 1

    From what I understand he wanted to climb the 4 sim and keep the rest in order. It was only the interpretation :)

  • but in case the second query would have to catch the others highlight=yes -the first 4 together with the highlight=no. Briefly it would be a general consultation removing the first 4 id with highlight yes

  • 1

    So this query lists all the sim minus the first 4, and makes a union with the query that returns all the não, I just can’t tell which is more performative, which one with Union or the previous one with subconsulta.

2


Use the following subconsulta:

SELECT *
FROM noticias n
WHERE id NOT IN (
  SELECT * FROM (
    SELECT id
    FROM noticias
    WHERE destacar = 'Sim'
    ORDER BY id LIMIT 4
  ) p);

Example working on SQL Fiddle.

  • 1

    in which case if I had 100 news 50 yes and 50 no NO would even appear in the results

  • 1

    I edited the answer, see if this is it @Arsomnolasco

  • If you are not MYSQL you can use TOP 4 in the sub-shipment, if I am not mistaken

  • worked, however I tried to put a limit at the end only that gave error ... ORDER BY id LIMIT 4 ) p) ORDER BY DESC LIMIT 6; ... know pq the error?

  • You need to inform the field after command ORDER BY, would look like this: ... ORDER BY id DESC LIMIT 6 @Arsomnolasco

1

To do this is easier programming. Make a normal select with the items you want and then check if the field is yes and remove

resultados = query(Select....

Contador = 0
Para Cada resultado Em resultados
{
    Se resultado.destaque == sim E Contador < 4 {
        remova resultado de resultados;
        Contador++
    }
}

  • $sql = "SELECT * FROM noticias WHERE ORDER BY idnoticia DESC LIMIT 3 "; $stmt->execute(); $displays = stmt->fetchAll();foreach ($displays as $u) {if $mostra.highlight == Highlighted AND $Counter < 4 { What is the function of removing result ? $Counter++ }} echo

Browser other questions tagged php sql

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