ORDER BY 1.1.1, 2.3.2, 10.2.3 3

[rejome]

I have a text field in MSSQL, query made with ACCESS, where some of the data are 1.1.1, 2.3.2, 10.2.3. Right now, an ORDER BY on that field is made and the result is:
1.1.1
10.2.3
2.3.2
...

I'd prefer to order them in a 'numeric' way, like this:
1.1.1
2.3.2
10.2.3

Is that possible?

 

[Krickles]

SELECT [fieldname]
FROM

ORDER BY PATINDEX('%.%', [fieldname])

Hope this helps.

Krickles | 1.6180

 

[rac2]

No, you must parse the string and convert the pieces to integers. This wont be easy, you might want to create a view with the parsed columns and a foreign key, when you need to sort the basic table join it to the view and used the integer columns to sort.

Parsing the second piece-

/*Eliminate the first and third pieces and the periods.*/
/*  Position of first period                           */
CHARINDEX('.',paragraph_index)

/*  Position of second period                          */
CHARINDEX('.',paragraph_index, CHARINDEX('.',paragraph_index)+1)

/*  The length of the second piece                     */
CHARINDEX('.',paragraph_index, CHARINDEX('.',paragraph_index)+1) - 
CHARINDEX('.',paragraph_index) - 1

/*  The second piece                                   */
SUBSTRING(paragraph_index,
          CHARINDEX('.',paragraph_index)+1,
          CHARINDEX('.',paragraph_index, CHARINDEX('.',paragraph_index)+1) - 
CHARINDEX('.',paragraph_index) - 1
          )

This would be one item in a SELECT list. Using the string functions you would construct two more expressions to extract the first and third pieces. This is what I mean by parsing the string.

Then cast each to an integer.

CAST( horrible expression above AS INT)

Because this is so messy and prone to error save it in a view.

CREATE VIEW ParagraphOrder (paragraph_id INT, i1 INT, i2 INT, i3 INT) AS
SELECT paragraph_id,
       CAST( first piece AS INT),
       CAST( second piece AS INT),
       CAST( third piece AS INT)
FROM Paragraphs

Then when you need to sort

SELECT explanation, revision, author
FROM Paragraphs p
JOIN ParagraphOrder po ON
     po.paragraph_id = p.paragraph_id
ORDER BY i1, i2, i3

Sort of makes me wish I had defined my table with three integer columns instead of one varchar column.


Surely this cannot be true!? I will be looking forward to seeing my answer proved woefully misguided.

 

[maswien]

Thinking of the comparison of all three numbers in the string, it should be:

select * from

ORDER BY cast( replace(stuff([fieldname], charINDEX('.', [fieldname]),1, '000'), '.', '00') as int)

 

[Krickles]

My suggestion works as long as the second and third sets of numbers are single digits (xxx.x.x). If you have multiple sets of multiple digits (e.g., xxx.xx.xxx) then you will have to use something much more complex.

Krickles | 1.6180

 

[maswien]

it should be

select * from myTable
order by
left([fiels name], charINDEX('.', [fiels name]) - 1) * 100 +
substring([fiels name], charINDEX('.', [fiels name])+1, charindex( '.', stuff([fiels name], charINDEX('.', [fiels name]),1, ''))- charINDEX('.', [fiels name])) * 10 +
right([fiels name], len([fiels name]) - charindex( '.', stuff([fiels name], charINDEX('.', [fiels name]),1, '')) - 1)

 

[rejome]

Well guys, a lot of good ideas here! But all of them don't work in Access. Yes I must do it with Access. Still have some ideas?

 

[ESquared]

It does work in Access if you can create the view on SQL Server... then link to the view in Access and use it as you would any table.

If you MUST do it in Access then refer to rac2's great message above, and look up the Instr and Mid functions in Access/VBA help.

Keep in mind that you can write a query that does essentially the same thing as a view. Refer to the query name just as you would any table name in your queries.

 

[rejome]

Oh yeah! Forgot the view. Thanks to all of you guys. Great help, as usual.

 

[Krickles]

I found a different way that seems efficient and gives the results as requested (opinions welcomed):

SELECT [fieldname]
FROM

ORDER BY CONVERT(NUMERIC, REPLACE([fieldname],'.',''))



Krickles | 1.6180

 

[vongrunt]

Add these values for testing:

1.1.25
11.2.4
1.1.241
11.23.1

Length of data w/o dots is variable, position of dots is variable, so...

 

[Krickles]

Agh! I was so happy...

Krickles | 1.6180

 

[maswien]

vongrunt,

I think we can think of various of situation, but it's should be reasonable, I guess. From the original example,

1.1.1
10.2.3
2.3.2

We have pretty strong reason to assume that there is 2 dot
in the string, and every number sparated by the string is
>=1 and <=10. That's what I did in my SQL.

 

[ESquared]

If t is the table and c is the field:

SELECT *
   FROM t
   ORDER BY
      Convert(int,Left(c,Charindex('.',c)-1)),
      Convert(int,SubString(c,Charindex('.',c)
         +1,Charindex('.',c,Charindex('.',c)+2)-Charindex('.',c)-1)),
      Convert(int,Right(c,Charindex('.',reverse(c))-1))

 

[maswien]

I should say Esquared's solution is a elegant one!

 

[ESquared]

The charindex function should have a parameter to tell it to find the nth occurrence! Everything could be much simpler.

For what it's worth, my answer is just the full realization of rac2's suggestion.

 

[josephwalter]

Here's a SQL statement that can be used in MS Access. t is the table name, c is the column name:

SELECT *
FROM t
ORDER BY CLng(Left(c,InStr(c,".")-1)),
CLng(Mid(c,InStr(c,".")+1,InStr(InStr(c,".")+1,c,".")-InStr(c,".")-1)),
CLng(Right(c,Len(c)-InStrRev(c,".",-1,1)));

 

[ESquared]

As josephwalter indirectly pointed out, my code should be altered as follows:

SELECT *
   FROM t
   ORDER BY
      Convert(int,Left(c,Charindex('.',c)-1)),
      Convert(int,SubString(c,Charindex('.',c)
         +1,Charindex('.',c,Charindex('.',c)+[b][red]1[/red][/b])-Charindex('.',c)-1)),
      Convert(int,Right(c,Charindex('.',reverse(c))-1))

I was assuming there would always be at least one character between the middle two periods, which is not necessarily a good assumption.