Limit record occurrences for each type

Question

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.

But I need to make it a single query. In this case, I need the first 10 occurrences of each type in the same query.

How can I do this? With CASE WHEN it is possible to make a counter for each type of material? I even did some tests, but without success. I guess my logic isn’t working on that question.

Unfortunately I could not extract an answer from the question below

Limit number of Group By occurrences

Answer 1

You can use the ROW_NUMBER grouped 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;

Answer 2

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

What would your SQL look like?

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

_{Reproduction: the @Sorack answer is fair to 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:

• UNION (Transact-SQL)
• SQL: Using UNION Operator and UNION ALL