CODEWITHSUNDEEP
t-sqlsql-server-2008sorting
TTCG
TTCG

Reputation: 9113

Sorting in TSQL

I am using SQL Server 2008.

I would like to sort my result by using the DetailRefNumber. However, I could not achieve to sort like 

2.1 
2.2 
2.3 
xxx
xxx
2.10
2.11
2.12

Could you please guide me how I could do it?

enter image description here Thanks.

Upvotes: 2

Views: 236

Answers (5)

EricZ
EricZ

Reputation: 6205

You may try PARSENAME

ORDER BY CAST(PARSENAME(DetailRefNumber,2) AS INT),CAST(PARSENAME(DetailRefNumber,1) AS INT)

Upvotes: 2

Chris
Chris

Reputation: 1

select *  
from yourTable  
order by  
assessmentRefNum,  
convert(int,right(detailRefNum,len(detailRefNum)-charindex('.',detailRefNum,1)));

Upvotes: 0

JonH
JonH

Reputation: 33183

I've never just tried this but can you try to CAST or CONVERT a column in the order by?

Try this...

SELECT
 ...
FROM
...
ORDER BY
 CAST(MyColumn AS Decimal(18,2))

This works:

CREATE TABLE #t
 (
  mycol varchar(20)
 )

INSERT INTO #t(mycol) VALUES('1.0')
INSERT INTO #t(mycol) VALUES('1.10')
INSERT INTO #t(mycol) VALUES('2.10')
INSERT INTO #t(mycol) VALUES('21.20')
INSERT INTO #t(mycol) VALUES('2.00')

SELECT * FROM #t ORDER BY CAST(mycol as Decimal(18,2))

DROP TABLE #t

enter image description here

Your's would become:

SELECT
   AssessmentID,
   AssessmentRefNumber,
   DetailRefNumber
FROM 
   Assessments
ORDER BY
   CAST(DetailRefNumber As Decimal(18,2))

edit

Just noticed the "version number sorting". Take exactly what I did and change the final part to use substring / what marc_s posted works pleasantly:

CREATE TABLE #t
 (
  mycol varchar(20)
 )

INSERT INTO #t(mycol) VALUES('1.0')
INSERT INTO #t(mycol) VALUES('1.2')
INSERT INTO #t(mycol) VALUES('1.11')
INSERT INTO #t(mycol) VALUES('2.10')
INSERT INTO #t(mycol) VALUES('21.20')
INSERT INTO #t(mycol) VALUES('2.20')
INSERT INTO #t(mycol) VALUES('2.00')
INSERT INTO #t(mycol) VALUES('2.11')
INSERT INTO #t(mycol) VALUES('2.2')

SELECT 
   * 
FROM #t 
ORDER BY 
  CAST(LEFT(mycol, CHARINDEX('.', myCol)-1) AS int), 
  CAST(SUBSTRING(myCol, CharINDEX('.', mycol)+1, 999) AS int)

Upvotes: 3

marc_s
marc_s

Reputation: 755401

If that DetailRefNumber will always be a number, separated by a dot, you could add two computed columns to that table which basically take that column apart into two separate, numeric values:

alter table dbo.YourTable
  add RefNrMajor AS CAST(LEFT(DetailRefNumber, CHARINDEX('.', DetailRefNumber) - 1) AS INT) PERSISTED

alter table dbo.YourTable
  add RefNrMinor AS CAST(SUBSTRING(DetailRefNumber, CHARINDEX('.', DetailRefNumber) + 1, 999) AS INT) PERSISTED

and now you can order by those new numeric columns:

SELECT (columns)
FROM dbo.YourTable
ORDER BY RefNrMajor, RefNrMinor

Upvotes: 5

GendoIkari
GendoIkari

Reputation: 11914

You need to use SUBSTRING and CAST...

order by 
    CAST(SUBSTRING(DetailRefNumber, 0, 1) as int),
    CAST(SUBSTRING(DetailRefNumber, 2, LEN(DetailRefNumber) - 2) as int)

This will first order by the number before the decimal, then by the number after the decimal, and treat 2.10 as greater than 2.3.

Upvotes: 1

Related Questions