CODEWITHSUNDEEP
sqlsql-server
David Tunnell
David Tunnell

Reputation: 7542

SELECT query not returning in desired order

I want to have the SELECTed query return in order by Number. This is currently what is being returned:

enter image description here

And here is the current query:

SELECT 
    [Number] + ' - ' + SUBSTRING([Title], 1, 40) AS [StoryName]
    ,[PK_Story]
FROM Story STY
INNER JOIN Task TSK ON TSK.StoryId = STY.PK_Story
INNER JOIN DailyTaskHours DTH ON DTH.TaskId = TSK.PK_Task
WHERE ProductId = @productIdParam
    AND DTH.ActivityDate BETWEEN @startDateParam
        AND @endDateParam
GROUP BY [Number]
    ,[Number] + ' - ' + SUBSTRING([Title], 1, 40)
    ,[PK_Story]
HAVING SUM(DTH.[Hours]) > 0
ORDER BY [Number] ASC

I am attempting to order by [Number] which is the CMIS-##. As you can see I have the ORDER BY attempting to do this but the resulting order is incorrect. The lower values 'CMIS-43' is not at the top. How do I fix this?

Edit:

This was ultimately my solution to deal with variable length of [Number]:

SELECT [Number] + ' - ' + SUBSTRING([Title], 1, 40) AS [StoryName]
        ,[PK_Story]
FROM Story STY
INNER JOIN Task TSK ON TSK.StoryId = STY.PK_Story
INNER JOIN DailyTaskHours DTH ON DTH.TaskId = TSK.PK_Task
WHERE ProductId = @productParam
      AND DTH.ActivityDate BETWEEN @startDateParam
            AND @endDateParam
GROUP BY [Number]
     ,[Number] + ' - ' + SUBSTRING([Title], 1, 40)
     ,[PK_Story]
HAVING SUM(DTH.[Hours]) > 0
ORDER BY CAST(SUBSTRING([Number], CHARINDEX('-',[Number]) + 1, LEN([Number])) AS INT)

Upvotes: 1

Views: 124

Answers (4)

JamieA
JamieA

Reputation: 2013

ORDER BY CAST(SUBSTRING([Number], 6, LEN([number])-5) AS INT)

Upvotes: 2

Dan Bracuk
Dan Bracuk

Reputation: 20804

The source of your problem is that you are trying to do numeric sorts on text. In other words, while the number 10 is greater than the number 9, the string '9' is greater than the string '10'.

To solve it, you will have to another field to your select and group by clauses. This field will use string functions to get the numeric part of the number field, and then cast it to an integer. I'll leave the details to you.

Upvotes: 0

Douglas Barbin
Douglas Barbin

Reputation: 3615

The problem is that your list is being sorted as a string, and you want it sorted numerically.

Try something like this:

SELECT [Number] + ' - ' + SUBSTRING([Title], 1, 40) AS [StoryName]
    ,[PK_Story], CAST(SUBSTRING([Number], 5, 3) AS int) AS [CMNumber]
FROM Story STY
INNER JOIN Task TSK ON TSK.StoryId = STY.PK_Story
INNER JOIN DailyTaskHours DTH ON DTH.TaskId = TSK.PK_Task
WHERE ProductId = @productIdParam
    AND DTH.ActivityDate BETWEEN @startDateParam
        AND @endDateParam
GROUP BY [Number]
    ,[Number] + ' - ' + SUBSTRING([Title], 1, 40)
    ,[PK_Story]
HAVING SUM(DTH.[Hours]) > 0
ORDER BY CMNumber ASC

Upvotes: 3

trf
trf

Reputation: 1394

[Number] is a string/varchar, so the ordering is lexicographic (alphabetical) rather than numeric.

Upvotes: 1

Related Questions