LIMIT IN A QUERY IN A FIELD

Asked

Viewed 119 times

0

I have a table where there are two fields with possible values: yes-no. The select I’m doing is this:

select 
      nome, 
      media, 
      escola as 'Particular', 
      bairro as 'COTA DE BAIRRO' 

from 
      alunos 
where 
      curso = 'Enfermagem' 

order by 
      media desc, nome asc, 
      (CASE WHEN bairro = 'Sim' THEN 0 ELSE 1 END), 
      (CASE WHEN escola = 'Sim' THEN 0 ELSE 1 END)

In the Particular field, return only 8 values with result 'Yes' and in the neighborhood field 2 values with result 'Yes'. You can’t do it with limit, because limit returns the limit of all the consulted lines, and I wanted you to limit only the number of results with two columns Yes

mysql sql database
  • 1

    And if the same record holds escola='sim' and bairro='sim'? It should count both in the 8 private records and in 2 Neighborhood?

  • Iiim, is that I’m doing a system of pre enrollment, then I wanted to return a ranking, as we noticed, and the rules of the edict states that needs to have 8 private school students and 2 students with neighborhood quota

  • So it doesn’t make much sense for him to be considered twice. If the student has already entered by studying in private school, should not take the place of a neighborhood quota, no?

  • has reason, had not thought about it, and in fact, in the previous notices the student with lower average were always the with neighborhood quota

2 answers

1

1. You can use the clause LIMIT {number of lines} or use the clause TOP {number of lines} or the clause ROWNUM =< {number of lines}

Reference: Using the LIMIT, TOP or ROWNUM clauses

2. You can perform two distinct select, one to recover the neighborhood and the other to recover the school, because you want to recover a different number of lines for each result, analyzing your problem, apparently the ORDER BY may be different, a great opportunity to further improve your code. You can also make a UNION to bring the two results (believe to be the best way).

Reference: Using UNION

3. It would be much more performative your query if instead of you using the CASE in the ORDER BY, you use the CASE in the SELECT.

Reference: Using the CASE Clause

0


It is possible to consult with a Union, excluding students who are already in the private quota to enter also the neighborhood quota.

with
 tb_particular as 
    (select nome, media, 'cota escola particular' as 'tipo_cota'
    from aluno
    where particular=true
    order by media desc
    limit 8)
select * from tb_particular
union
(select nome, media, 'cota bairro' as 'tipo_cota'
    from aluno
    where cota_bairro=true
        and  (nome not in (select nome from tb_particular))
    order by media desc
    limit 2)

See working here

Remarks:

  • the 'with' I used in the query is mysql 8.0 resource. If you do not is using this version would have to make a Union of the first select (the one assigned to tb_particular) with the second select and repeat all the first select as parameter pro 'not in';
  • i used the 'name' column in not, but in the real case you would use the
    table.

  • this is exactly what I need, however, is not supporting the with and the instructions of what to do with Union have not become very clear, you have to clarify better, please

  • So, you can get around this without using with by repeating the first select within the parentheses not in(...). Maybe you have another way, not mysql use. Monday I can take a look. I didn’t understand the doubt about Union.

  • I said it wrong, I’m sorry. You said that if I couldn’t stand the with (can’t stand it) I had to do another instruction to replace it, this instruction that wasn’t clear to me.

  • The same select that you used to select the quota students by private school, you have to use in the select of neighborhood quota students, so that they are not included in the latter. With you prevent this. Without the with you can do so https://www.db-fiddle.com/f/9fdKrGHtxfgqMwfh4EWTxk/0

Browser other questions tagged mysql sql database

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