Query zeroing column when applying order by - Mysql

Asked

Viewed 46 times

0

I am with a query that when ordering it in ASC it Zera a column of my table, but if I sort in DESC displays the values normally.

This is the query:

SELECT 
`tab_medicamentos`.`id`, `tab_medicamentos`.`classe_terapeutica`, `tab_medicamentos`.`apresentacao`, `tab_medicamentos`.`produto` as `nome`, `tab_medicamentos`.`pmc_18_perc` as `preco`, `tab_medicamentos`.`img` as `imagens`, `tab_medicamentos`.`margem`, `tab_medicamentos`.`margem_desconto`, `tab_medicamentos`.`restrcao_hospitalar` 
FROM 
`tab_medicamentos` 
WHERE 
`classe_terapeutica` LIKE '%M01C0%' ESCAPE '!' 
OR `classe_terapeutica` LIKE '%L01B0%' ESCAPE '!' 
OR `classe_terapeutica` LIKE '%N02A0%' ESCAPE '!' 
OR `classe_terapeutica` LIKE '%N02B0%' ESCAPE '!' 
OR `classe_terapeutica` LIKE '%N01A2%' ESCAPE '!' 
OR `classe_terapeutica` LIKE '%N01B1%' ESCAPE '!' 
OR `classe_terapeutica` LIKE '%M01A1%' ESCAPE '!' 
OR `classe_terapeutica` LIKE '%M02A0%' ESCAPE '!' 
OR `classe_terapeutica` LIKE '%R05A0%' ESCAPE '!' 
OR `classe_terapeutica` LIKE '%A11D4%' ESCAPE '!' 
OR `classe_terapeutica` LIKE '%A03D0%' ESCAPE '!' 
OR `classe_terapeutica` LIKE '%M01A3%' ESCAPE '!' 
OR `classe_terapeutica` LIKE '%B01C1%' ESCAPE '!' 
OR `classe_terapeutica` LIKE '%M03B0%' ESCAPE '!' 
OR `classe_terapeutica` LIKE '%N02C9%' ESCAPE '!' 
ORDER BY `pmc_18_perc` ASC

When I put the ORDER BY ASC he Zera all values of this column 'pmc_18_perc', but when I order in DESC appears the values normally.

What can it be?

php mysql query order-by

  • Are you sure you’ve wiped them all out or just in the first few records? Why ORDER BY ASC will bring the data starting from "minor", zeros, spaces, etc first... may be that the filled data are in the next records, came to look all the result of select?

  • did not reset all values, just ordered those with "0".

  • Try WHERE, pmc_18_perc IS NOT NULL

  • I forgot to mention that because I knew it would be my first chance. I checked at the end of the result, and from the first line to the last the value was reset! I never saw that! I put IS NOT NULL and it hasn’t changed a bit.

1 answer

0

Have you tried ordination at a higher level?

Ex:

select * from (SELECT tab_medicamentos.id, tab_medicamentos.classe_terapeutica, tab_medicamentos.apresentacao, tab_medicamentos.produto as nome, tab_medicamentos.pmc_18_perc as preco, tab_medicamentos.img as imagens, tab_medicamentos.margem, tab_medicamentos.margem_desconto, tab_medicamentos.restrcao_hospitalar FROM tab_medicamentos WHERE classe_terapeutica LIKE '%M01C0%' ESCAPE '!' OR classe_terapeutica LIKE '%L01B0%' ESCAPE '!' OR classe_terapeutica LIKE '%N02A0%' ESCAPE '!' OR classe_terapeutica LIKE '%N02B0%' ESCAPE '!' OR classe_terapeutica LIKE '%N01A2%' ESCAPE '!' OR classe_terapeutica LIKE '%N01B1%' ESCAPE '!' OR classe_terapeutica LIKE '%M01A1%' ESCAPE '!' OR classe_terapeutica LIKE '%M02A0%' ESCAPE '!' OR classe_terapeutica LIKE '%R05A0%' ESCAPE '!' OR classe_terapeutica LIKE '%A11D4%' ESCAPE '!' OR classe_terapeutica LIKE '%A03D0%' ESCAPE '!' OR classe_terapeutica LIKE '%M01A3%' ESCAPE '!' OR classe_terapeutica LIKE '%B01C1%' ESCAPE '!' OR classe_terapeutica LIKE '%M03B0%' ESCAPE '!' OR classe_terapeutica LIKE '%N02C9%' ESCAPE '!' ) as tmp

order by tmp.pmc_18_perc desc

  • I tried, but it is returning the following error: #1054 - Unknown 'tmp.pmc_18_perc' column in 'order clause'. So... When I consult with only one clause he does everything as expected, now when he puts another clause he Zera all values... I do not understand this.

Browser other questions tagged php mysql query order-by

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