Group by And order by. [Resolved]

Posted by Samy33 under Sql Server on 5/17/2010 | Views : 1074 | Status : [Member] | Replies : 2
I am having 1000 records in my database.I need to sort the records.i write the Query but it gives some uneven ascending order.

Sql Query:

select mpp_reference from manpower_plan_master order by mpp_reference asc

This is my table

mpp_reference varchar no 16 no no no SQL_Latin1_General_CP1_CI_AS
mpp_date datetime no 8 no (n/a) (n/a) NULL
mpp_for_year varchar no 50 no no no SQL_Latin1_General_CP1_CI_AS
mpp_dept numeric no 9 18 0 no (n/a) (n/a) NULL
mpp_domain varchar no 50 no no no SQL_Latin1_General_CP1_CI_AS
mpp_revision_no numeric no 9 18 0 no (n/a) (n/a) NULL
mpp_revision_date datetime no 8 yes (n/a) (n/a) NULL
mpp_requested_by varchar no 50 no no no SQL_Latin1_General_CP1_CI_AS
mpp_additional_info varchar no 3000 yes no yes SQL_Latin1_General_CP1_CI_AS
mpp_location varchar no 25 yes no yes SQL_Latin1_General_CP1_CI_AS
mpp_status varchar no 50 yes no yes SQL_Latin1_General_CP1_CI_AS
status varchar no 1 yes no yes SQL_Latin1_General_CP1_CI_AS
user_id varchar no 16 no no no SQL_Latin1_General_CP1_CI_AS
ip_address varchar no 50 no no no SQL_Latin1_General_CP1_CI_AS
mpp_branch numeric no 9 18 0 yes (n/a) (n/a) NULL


output:

10
10
10
11
12
13
13
13
14
14
15
15
15
15
16
16
16
16
16
17
17
18
18
19
19
19
2
20
20
21
22
22
23
23
23
23
3
4
5
5
6
6
6
6
7
8
9

Expected output:
1
2
3
4
5
6
7
8 like this...

If anybody knows the answer ,please reply to this question.......




Responses

Posted by: PandianS on: 5/17/2010 [Member] [MVP] Silver

Up
0
Down

Resolved
Hi

You have to cast the column as given below...
select mpp_reference from manpower_plan_master order by CAST(mpp_reference AS INT) asc
Please ensure that The column "mpp_reference" doesn't have any character data except numbers.

But, The casting is very expensive in this case.

Cheers


Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Samy33, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Samy33 on: 5/18/2010 [Member] Starter

Up
0
Down
Thank you very much

Samy33, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response