How to select distinct month- year order by month-
-
Hi,
I would like to select only distinct month-year(together) from a table. so
I have write a quary as
"select distinct datename(month,productiondate)+'-'+ datename(year,productiondate) from tblmytable"
Its ok. but shows result as follows:
July-2003
October-2003
September-2003
But i want it to be sorted according to year month wise, like :
July-2003
September-2003
October-2003
How to do that one...?Can u help me on that
..Better Than Before... -
Maybe I'm missing something. can't you just order by productiondate?
-
no, I have to find only distinct year and month only not date ...
..Better Than Before... -
How about:
select datename(month,productiondate)+'-'+ datename(year,productiondate)
from tblmytable
group by datename(month,productiondate)+'-'+ datename(year,productiondate)
HTH
Ray Higdon MCSE, MCDBA, CCNA
------------
Ray Higdon MCSE, MCDBA, CCNA -
quote:
How about:select datename(month,productiondate)+'-'+ datename(year,productiondate)
from tblmytable
group by datename(month,productiondate)+'-'+ datename(year,productiondate)
HTH
Ray Higdon MCSE, MCDBA, CCNA
May be you r missing my req. This also showing result as
July-2003
October-2003
September-2003
i want it to be :
July-2003
September-2003
October-2003
.. be sorted according to year, month wise
..Better Than Before... -
quote:
How about:select datename(month,productiondate)+'-'+ datename(year,productiondate)
from tblmytable
group by datename(month,productiondate)+'-'+ datename(year,productiondate)
HTH
Ray Higdon MCSE, MCDBA, CCNA
May be you r missing my req. This also showing result as
July-2003
October-2003
September-2003
i want it to be :
July-2003
September-2003
October-2003
.. be sorted according to year, month wise
..Better Than Before... -
Ah, didn't catch what you meant, well, without displaying that proddate, one way you could do this is to use a temp table:
create table tblmytable (productiondate datetime)
insert into tblmytable
select '2003-07-01' union all
select '2002-08-01' union all
select '2002-08-01' union all
select '2003-09-01' union all
select '2001-08-01' union all
select '2003-10-01'
select distinct datename(month,a.productiondate)+'-'+ datename(year,a.productiondate)as Date, a.productiondate
into ##newtable
from tblmytable a
order by a.productiondate
select Date from ##newtable
Probably a better way but when you turn that into a string, alphabetical is the only way to order it without utilizing the display of the date.
HTH
Ray Higdon MCSE, MCDBA, CCNA
------------
Ray Higdon MCSE, MCDBA, CCNA -
Thx ray_higdon
It is working...
..Better Than Before... -
ray_higdon
One problem here boss!
It is showing unexpected ans for data
2003-07-03
2003-07-09
2003-09-26
2003-10-27
That is :
July-2003
July-2003
September-2003
October-2003
..Better Than Before... -
It might be ok if the quary is as follows:
select distinct datename(month,a.productiondate)+'-'+ datename(year,a.productiondate)as Date, a.productiondate
into ##newtable
from tblDailyConsumption a
order by a.productiondate
select distinct Date from ##newtable
However , Thx all for helping me,specially ray_higdon.
..Better Than Before... -
SELECT MonthYr
FROM
(SELECT DISTINCT DATENAME(m,ProductionDate) + '-' + DATENAME(yy,ProductionDate) MonthYr, CONVERT(char(7),ProductionDate,120) MoYr
FROM MyTable) d
ORDER BY MoYr--Jonathan
--Jonathan -
It Seems to be better one,
Jonathan !
..Better Than Before...
Jonathan !Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply