Tuesday 26 October 2010

Order by Month


Sometime we stuck to get our desire output.So its good practice we kept such information where its easily available.
Good Luck.
e.g.
SELECT TO_CHAR (TRUNC (dateordered, 'mm'), 'Month'),
MEDIAN (dateshipped - dateordered)
FROM orders
GROUP BY TRUNC (dateordered, 'mm')

ORDER BY TRUNC (dateordered, 'mm');

e.g.
select a.id,a.name,TO_CHAR(trunc(ah.date_viewed,'mm'),'Mon-yyyy') "Month Downloaded",count(*) "No of times Viewed"
from articleviewhistory ah,article a
where ah.article_id = a.id
and ah.article_id in (1085,984,961,900,638,630,628,624,613,611)
group by a.id, a.name, trunc(ah.date_viewed,'mm')
order by 2,trunc(ah.date_viewed,'mm');


e.g.
select to_char(day,'MON-YYYY'), sum(daily_growth_mb)
from db_growth
group by to_char(day,'MON-YYYY')
order by to_date(to_char(day,'MON-YYYY'),'MM-YYYY') desc;
or best one

select to_char(trunc(date_created,'MM'),'MON-YYYY'),count(*) from twitter
group by trunc(date_created,'MM')
order by trunc(date_created,'MM')

No comments: