Case in mysql - display certain quantity

Question

Case in mysql - display certain quantity

Navigation

#1 by (2 votes)

1

I'm having a pretty doubt in an sql:

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

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

Here's the 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

asked by anonymous 17.03.2016 / 14:32

1 answer

Store vector in SQLite What is the relationship between OAuth and Rest?

score 2 · Accepted Answer

The answer follows, I had 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)