Select, by quantity, items that match conditions

Asked

Viewed 192 times

3

I have a question when it comes time to mount a query.

I’ll set up a fictional scenario to demonstrate.

I have the table quantidadeQuestoes with the fields:

  • cod_nivel
  • cod_categoria
  • cod_difficulty
  • amount

And I have the table questoes with the fields:

  • cod_nivel
  • cod_categoria
  • cod_difficulty
  • description

My question is. How to get from the table questoes the quantity specified in the field quantidade table quantidadeQuestoes where other fields are matched.

With my current query I am taking all items and not just the specified amount in the quantity field, which is what I need:

SELECT
    qt.descricao
FROM
    quantidadeQuestoes qq
INNER JOIN
    questoes qt ON
    qq.cod_nivel = qt.cod_nivel AND
    qq.cod_categoria = qt.cod_categoria AND
    qq.cod_dificuldade = qt.cod_dificuldade

It is for me to put together a test that contains so many questions of a level + category + difficulty.

sql sql-server

  • Joaopaulo, the table quantityTuestoes stores how many questions there are for the key (cod_nivel, cod_categoria and cod_dificuldade)?

  • I can’t remember if this syntax is possible, but you’ve tried putting a top qq.quantidade in the query?

  • @Cantoni did not understand your question.

  • I couldn’t figure out what part of the query would fit this top @Luishenrique. Where I tried SQL did not allow

  • My question is about your model. I am understanding that the table quantityTheses stores the amount of questions for the same cod_nivel, cod_categoria and cod_dificuldade. Already the table questions stores the questions themselves. I understood right?

  • Exactly @Cantoni.

  • 1

    Okay, I’m going to post an answer here. Anyway, from the template posted, I’m not seeing any reason for the existence of the table quantityThese. In the answer I explain.

  • If the table quantityTheses simply stores as many records of questions of the other table I don’t understand what you want. What I had understood is that to create a proof creates records in the table quantityTuestos specifying the level, category and difficulty and the number of questions desired.

  • You got it right @Luishenrique. The number of questions is already pre-defined. From it I mount a test with the amount of questions specified with level + category + difficulty

  • You tried to make select top qq.quantidade qt.descricao from...? Anyway this would always take the same questions (the first N based on the other filters), I think the system to capture the issues and generate the evidence could be reworked and not treated in the database.

Show 5 more comments

1 answer

5


If I understand correctly, you will need a subquery. I got a solution using ROW_NUMBER, which numbers the lines found. The PARTITION BY is being used to reset the count for each table row quantidadeQuestoes. Stayed like this:

SELECT descricao
FROM (
  SELECT 
    ROW_NUMBER() OVER(PARTITION BY q.cod_nivel, q.cod_categoria, q.cod_dificuldade ORDER BY q.cod_nivel DESC) AS num,
    q.[cod_nivel], 
    q.[cod_categoria], 
    q.[cod_dificuldade], 
    q.[descricao],
    qq.[quantidade]
  FROM questoes q
    INNER JOIN quantidadeQuestoes qq
    ON qq.cod_nivel = q.cod_nivel AND
    qq.cod_categoria = q.cod_categoria AND
    qq.cod_dificuldade = q.cod_dificuldade 
) sq
WHERE sq.num <= sq.quantidade

Behold demo on sqlfiddle.

  • I removed my answer, it was wrong. Also, this answer was very elegant. ROW_NUMBER and PARTITION have already saved me several times.

  • @bfavaretto, did not understand this clause Where sq.num <= sq.quantity, what is the logic of this condition?

  • 2

    The logic is to ensure that the number of records returned per key ( cod_nivel, cod_categoria and cod_dificuldade) is less than the amount. If you don’t, there will be more records coming in than you want. The query, at the end, ensures that you will always select X record from the QUESTOES table, where X is the quantity field.

  • 2

    Exactly, @Cantoni was faster than me :) Try running only the query from within that you will understand.

  • 1

    To understand, run the query from within and see how the field in one is generated. When you do this, you will understand the reason for (sq.num <= sq.amount)

  • 2

    Gosh, we’re sync @bfavaretto. :-)

Show 1 more comment

Browser other questions tagged sql sql-server

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