Sort by SQL numbering

Asked

Viewed 91 times

4

I have a table with columns ID and ReqNorma filled with a following standard:

inserir a descrição da imagem aqui

When I perform the query to filter them, I receive values that are not in order because some of them were registered out of order:

select Id,ReqNorma 
from TABLE 
where Norma = 'ISO 9001' 
ORDER BY ReqNorma

inserir a descrição da imagem aqui

What would be the best way to make this ORDER BY filter to display results numerically?

sql

  • wouldn’t just be ordering by id?

  • Which one SGBD?

  • As I understand it, you need a numerical rather than alphanumeric sorting by prefixing the string contained in the Reqnorma field. Maybe an ORDER BY left(Reqnorma, position(' in Reqnorma))::Numeric can solve, depending on the DBMS you are using. You may also need to remove the '.'.

3 answers

3


It doesn’t seem like a very nice solution, but I tested it here and apparently it worked.

select Id,ReqNorma 
from TABLE 
where Norma = 'ISO 9001' 
ORDER BY CAST( SUBSTRING(ReqNorma, 1, charindex('.', ReqNorma) -1) AS INT), ReqNorma

Explanation:

charindex('.', Reqnorma) -1)

Get the index of the first '.'

SUBSTRING( Reqnorma, 1, [previous expression] )

Take the part of the string equivalent to the start to the first '.'

Example:

  • 10.2 would become 10
  • 2.1 would turn 2
  • 4.0 would become 4

CAST( [previous expression] as int):

Converts the returned value to integer, so that the sorting is numerical rather than alphanumeric.

  • 1

    Apparently it does, but it would be interesting for you to improve the quality of your response so that other people can understand what you are doing.

  • Thanks for the feedback, I’ll edit it right now

2

Try the following query and check if it is the desired ordering:

select Id,ReqNorma 
from TABLE 
where Norma = 'ISO 9001' 
ORDER BY Id ASC

  • Your query sorts by Id, by the question he wants you to sort by the number that is at the beginning of the Reqnorma. Ex: Imagine that standard 1.0 was registered, then 1.1, then 2.0 and finally 1.2. Ordering by Id would be "1.0; 1.1; 2.0; 1.2", but actually he wants "1.0; 1.1; 1.2; 2.0"

  • It helped me sort the other items out of order, but items 10.1, 10.2 and 10.3 continue to appear first in the filtering. OBS: Filtering by Reqnorma

  • For this ordination, only order by will not solve. The ideal is to separate these fields, leaving a numerical column for sorting and another text for the description. It has SQL commands that help, like SUBSTRING and REPLACE, you can do all the adjustment with a nested update in a select using these commands....

0

So Intendi you need the sequence, use DESC after order by

Example;

 declare @TABLE  table(id int, Norma varchar(30), ReqNorma varchar(50))
 insert into @TABLE select 106, 'ISO 9001', '4.2 entendo as
 necessidades...' insert into @TABLE select 107, 'ISO 9001', '4.3
 Determinndo o escopo...' insert into @TABLE select 108, 'ISO 9001',
 '4.4 Sistema de Gestão...' insert into @TABLE select 109, 'ISO 9001',
 '5.1.1 Liderença e...'


 select Id,ReqNorma from @TABLE where Norma = 'ISO 9001' ORDER BY
 ReqNorma desc

If not, please enter more details!

Browser other questions tagged sql

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