Limit occurrences of records for each type

Navigation
3

I need to limit the number of occurrences of each type of material I have in my database.

Follow the query below with what I want to do: 

SELECT TOP 10 ID, NOME, TIPO FROM TB_MATERIAIS WHERE TIPO = 1
SELECT TOP 10 ID, NOME, TIPO FROM TB_MATERIAIS WHERE TIPO = 2
SELECT TOP 10 ID, NOME, TIPO FROM TB_MATERIAIS WHERE TIPO = 3
SELECT TOP 10 ID, NOME, TIPO FROM TB_MATERIAIS WHERE TIPO = 4

As you can see, I want the first 10 occurrences of each type of material in my query.

How can I do this? With CASE WHEN is it possible to make a counter for each type of material? I even did some testing, but without success. I think my logic is not working out.

Unfortunately I could not extract an answer from the question below

Limit number of occurrences of Group By

    
asked by anonymous 16.11.2016 / 14:55

2 answers

3

You can use ROW_NUMBER with grouping as follows: 

SELECT x.*
  FROM(SELECT ROW_NUMBER() OVER(PARTITION BY m.TIPO  ORDER BY m.ID) as sequencia,
              m.*
         FROM TB_MATERIAIS m) x
 WHERE x.sequencia <= 10;
    
16.11.2016 / 15:19
2

Use UNION ALL , which will combine results without execution at the end of a distinct bringing the data even if there is repetition, I believe that in your case this already solves. If you do not want repetition use only UNION

  

What would your SQL look like then? 

SELECT TOP 10 ID, NOME, TIPO FROM TB_MATERIAIS WHERE TIPO = 1
UNION ALL
SELECT TOP 10 ID, NOME, TIPO FROM TB_MATERIAIS WHERE TIPO = 2
UNION ALL
SELECT TOP 10 ID, NOME, TIPO FROM TB_MATERIAIS WHERE TIPO = 3
UNION ALL
SELECT TOP 10 ID, NOME, TIPO FROM TB_MATERIAIS WHERE TIPO = 4

The server: @Sorack response is just for SQLServer and perfect, but, my server for several banks, I believe that the diversity of correct answers and that solve the problem are valid.

References:

16.11.2016 / 14:59
How to rotate an image without knowing the angle of inclination? Compare two list