CODEWITHSUNDEEP
sqlsql-servert-sql
raupach
raupach

Reputation: 3102

Problem with DISTINCT, SELECT and SORT in TSQL

maybe anyone can help me out with my SELECT statement on MS SQL Server. I am not very skilled in (T)SQL.

I have a table called RECORDS and with a DATETIME column called [BEGIN]. Now I would like to get some nvarchars which look like this "December 08, January 09, February 09"..

I came up with the following myself.

SELECT DISTINCT DATENAME(MONTH, [BEGIN]) + ' ' + SUBSTRING(DATENAME(YEAR, [BEGIN]),3,4) 
FROM RECORDS

However this is unsorted I would like to have the result set ordered from first to last.

February 09
January 09
December 08

Anyone?

Upvotes: 1

Views: 719

Answers (5)

Squirrel
Squirrel

Reputation: 24813

select  datename(month, yyyymm) 
    + ' ' 
    + right(convert(varchar(10), yyyymm), 2)
from
(
    select  dateadd(month, datediff(month, 0, [BEGIN]), 0) as yyyymm
    from    yourtable
    group by dateadd(month, datediff(month, 0, [BEGIN]), 0)
) a
order by yyyymm desc

Upvotes: 1

anishMarokey
anishMarokey

Reputation: 11387

what about 

with tbldate AS 
(
select distinct DATENAME(MONTH, date) + ' ' + SUBSTRING(DATENAME(YEAR, date),3,4)  as date
from dbo.tbldate  
)
select * from tbldate
order by date desc

Upvotes: 1

MRFerocius
MRFerocius

Reputation: 5629

SELECT DISTINCT DATENAME(MONTH, [BEGIN]) + ' ' + SUBSTRING(DATENAME(YEAR, [BEGIN]),3,4) FROM RECORDS
ORDER BY 1 DESC

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425843

SELECT  DATENAME(MONTH, DATEADD(month, b_month - 1, 0)) + ' ' + SUBSTRING(CAST(b_year AS VARCHAR), 3, 4)
FROM    (
        SELECT  DISTINCT YEAR([BEGIN]) AS b_year, MONTH([BEGIN]) AS b_month
        FROM    RECORDS
        ) q
ORDER BY
        b_year, b_month

Upvotes: 1

BBlake
BBlake

Reputation: 2398

Add ORDER BY [BEGIN] DESC

Upvotes: 0

Related Questions