Mysql case - display a certain amount

Asked

Viewed 83 times

1

I’m having a big doubt in a sql:

The website is divided into 3 categories, each category is highlighted, only that the customer has requested that wants to limit the amount of product highlighted by category.

It would look like this: I have 100 records within this category, 50 of them are highlights and 50 are not, I need to make an sql that of these 50 highlights, are displayed the last 10 registrants (limit of 10) and the rest of the 50 records that are not highlights are also displayed. I’ve been thinking about getting married, but I have no idea how to give Limit inside the case. Has anyone ever had to do something similar or has any hint to pass on??

Follow SQL I already have:

SELECT id, id_user, lead_size, titulo_size_interno, lead_size_interno, 
   imagem_interno, titulo_size, id_categoria, tipo_interno, destaque_interno, 
   titulo, slug, lead, imagem, tipo 
FROM tb_post 
WHERE ativo = 1 AND data_post <= NOW() AND ultima = 0 
 AND id_categoria IN(5)  ORDER BY ordem_interno DESC, id DESC

I’ve been thinking about something like this (I’ve never used the case syntax, it’s an example): 

SELECT id, id_user, lead_size, titulo_size_interno, lead_size_interno, 
  imagem_interno, titulo_size, id_categoria, tipo_interno, destaque_interno, 
  titulo, slug, lead, imagem, tipo 
FROM tb_post 
WHERE ativo = 1 AND data_post <= NOW() AND ultima = 0 
  AND id_categoria IN(5)  
ORDER BY ordem_interno DESC, id DESC case(destaque_interno = 1 limit 10)
mysql
  • 1

    The view will be 10 highlights + 50 no highlights, or 10 highlights + 90 records not selected as the 10 highlights?

  • Good question, the exhibition should be: I have 100 records, of these 100, 20 are highlights, only I marked that I want to display only 5 highlights of these 20, so the other 15 that would be left should be displayed below with the not featured. It seemed?

1 answer

2


follow answer, I ended up having to make a Union :)

(SELECT p.id, 
        p.titulo, 
        p.id_categoria,
        c.quantidade_destaque,
        p.destaque_interno,
        c.nome
        FROM tb_post p 
        JOIN tb_post_categoria c ON p.id_categoria = c.id
        WHERE p.ativo = 1 
        AND p.data_post <= NOW() 
        AND p.ultima = 0 
        AND c.id =3
        AND p.destaque_interno = 1
        ORDER BY p.id DESC LIMIT 2)
        UNION
(SELECT p.id, 
        p.titulo, 
        p.id_categoria,
        c.quantidade_destaque,
        p.destaque_interno,
        c.nome
        FROM tb_post p 
        JOIN tb_post_categoria c ON p.id_categoria = c.id
        WHERE p.ativo = 1 
        AND p.data_post <= NOW() 
        AND p.ultima = 0 
        AND c.id = 3
        ORDER BY p.id DESC)

Browser other questions tagged mysql

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