ORDER BY from MIN to MAX

Question

I'm new to SQL Server. I have a little problem and I can't look it up. The point is that I can't get the order of data in my column STATEID, I want to order it from min to max. I'm using the ORDER BY or ASC and it is not working. In what other way could I get the exact sequence min to max out of my column STATEID. I'm using SQL Server 2008.

Here's an example of my code.

ALTER PROCEDURE [dbo].[basicGatherSelect]
    @StateID            nvarchar(5),    
    @Name               nvarchar(50)
AS
BEGIN 
    BEGIN TRANSACTION 

    SET NOCOUNT ON;

    SELECT  
       StateID, name
    FROM 
        tblState 
    ORDER BY 
        STATEID ASC

    IF  @@error <> 0
    BEGIN
            ROLLBACK
            RAISERROR ('PROBLEM', 16, 1)
            RETURN
    END

    COMMIT
END

Results:

10 Liban
3 Rusia
4 Letonija
5 Austrija
7 BARAZIL
8 Njemacka
9 Argentina
1 Srbija
2 Bosna

I posted now. I would send a picture but I do not have reputation — , Jul 11 '14 at 18:28
Are you using NVARCHAR for STATEID? If they are all numbers use INT. — Kunal B., Jul 11 '14 at 18:32
That is not the problem. But it's not a good practice. Try to run the SELECT statement with ORDER BY STATEID as a query and not as SPROC. — Kunal B., Jul 11 '14 at 18:40
[Bad habits to kick : choosing the wrong data type](https://sqlblog.org/2009/10/12/bad-habits-to-kick-choosing-the-wrong-data-type) - you should always use the most appropriate data type - that's what they're there for, after all! If you have **numbers** - then please use a **numeric** data type so you can sum them it and order them correctly! Don't just use strings for everything out of sheer lazyness!! — marc_s, Jul 11 '14 at 18:52
Why are you passing **@StateID** and **@Name** when you're not using those variables at all? Try commenting out those 2 lines and see if it sorts properly. — Johnny Bones, Jul 11 '14 at 18:55
In addition to the sorting I would recommend you look at using try/catch instead. Also, giving an error message with some information about what happened is far better than "I got an error". — Sean Lange, Jul 11 '14 at 19:03
Convert the STATEID into INT and then sort. `CAST(STATEID AS INT) ASC` — Jesuraja, Jul 11 '14 at 20:55

score 1 · Accepted Answer · answered Jul 11 '14 at 20:36

1

Try this:

ALTER PROCEDURE [dbo].[basicGatherSelect]
    @StateID            nvarchar(5),    
    @Name               nvarchar(50)
AS
BEGIN 
    BEGIN TRANSACTION 

    SET NOCOUNT ON;

    SELECT  
       StateID, name
    FROM 
        tblState 
    ORDER BY 
        CAST(STATEID AS INT) ASC

    IF  @@error <> 0
    BEGIN
            ROLLBACK
            RAISERROR ('PROBLEM', 16, 1)
            RETURN
    END

    COMMIT
END

answered Jul 11 '14 at 20:36

Jesuraja

3,774
4
24
48

When I changed the data type to int sorting has successfully passed! Does this mean that when the data type nvarchar and when the column contains numbers it can not be sorted from smallest to largest – Jul 12 '14 at 06:41

score 0 · Answer 2 · edited May 23 '17 at 11:45

0

If you have no control over how the parameters are coming to you (so you cant change it to int)

Ordering by a string, will cause it to treat additional characters into consideration like you would when ordering something alphabetically.

If you had something which had 21 items...it would go...

1, 10, 11, 12....etc, 2, 20, 21, 3, 4...etc

You could always make a cast to int in which to sort by (assuming the ID will always be a number):

ORDER BY 
    CAST(@StateID AS INT) ASC

More Information On This Post: SQL ORDER chars numerically

edited May 23 '17 at 11:45

Community

1
1

answered Jul 11 '14 at 18:37

PWilliams0530

170
1
12

When I changed the data type to int sorting has successfully passed! Does this mean that when the data type nvarchar and when the column contains numbers it can not be sorted from smallest to largest – Jul 12 '14 at 06:42

ORDER BY from MIN to MAX

2 Answers2