sorting problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

sorting problem

Author

Topic

ereader
Yak Posting Veteran

50 Posts

Posted - 2003-04-15 : 06:32:07

Hi ALL

I have a query to sort the column value by year and month
0304
0212
0211
---------------------------------------------------
SELECT
RIGHT(year1,2)+''+ month1 'Day'
,SUM(Ire)AS 'IE', SUM(Final)AS 'FQ',SUM(Others) AS 'Others',SUM(NA) AS 'NA'
FROM
(
SELECT COUNT(ref)AS 'REF'
,CAST(DATEPART(mm,date)AS VARCHAR(11))AS 'month1'
,CAST(DATEPART(yy,date)AS VARCHAR(11))AS 'year1'
,Ire=CASE bm.company_id WHEN 'IE' THEN COUNT(ref) ELSE 0 END,
Final=CASE bm.company_id WHEN 'FQ' THEN COUNT(ref) ELSE 0 END,Others=CASE bm.company_id WHEN
ISNULL(company_id,0) THEN 0 ELSE COUNT(ref) END,NA=CASE bm.company_id WHEN '' THEN
COUNT(ref) ELSE 0 END
,company_id
FROM booking_master bm
GROUP BY CAST(DATEPART(yy,date)AS VARCHAR(11)),CAST(DATEPART(mm,date)
AS VARCHAR(11)),company_id
)
TABLE1
GROUP BY year1,month1
ORDER BY year1 DESC,month1 DESC

--- result as follows

038 0
037 0
034 10
033 13
032 12
031 19
029 0
028 0
027 0
026 0
025 0
024 0
0212 8
0211 2

I am counting the number of REF against the each companyid
but the problem is that i want the year value and month value in a single column but it giving not in a sorted manner and i am looking in the following manner

0304
0212
0211
0210

Updated

YEAR MONTH
----------------
2030 8
2009 8
2003 4
2003 3
2003 2
2003 1
2002 12
2002 11
2002 10
2002 9
2002 8
2002 7
2002 6
2002 5
2002 4
2001 9
1900 1

I want the above result in a SINGLE column(DAY) sorted according to the year and month

Edited by - ereader on 04/15/2003 07:40:42

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-04-15 : 09:34:11

Nothing major, its sorting both year1 and month1 by their ASCII values, since they have been cast to varchar:

quote:
CAST(DATEPART(mm,date)AS VARCHAR(11))AS 'month1'
,CAST(DATEPART(yy,date)AS VARCHAR(11))AS 'year1'

Just add this to the end:

ORDER BY CAST(year1 AS INT) DESC, CAST(month1 AS INT) DESC

OS

ereader
Yak Posting Veteran

50 Posts

Posted - 2003-04-15 : 09:52:29

Cool One

quote:

Just add this to the end:
ORDER BY CAST(year1 AS INT) DESC, CAST(month1 AS INT) DESC
OS

Thanks mohdowais

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources