ROW_Number Help
-
Out Put of query result is
Rn ColDate
1 2010-02-01 00:00:00.000
2 2010-02-01 00:00:00.000
3 2010-02-01 00:00:00.000
4 2010-02-01 00:00:00.000
5 2010-02-02 00:00:00.000
1 2010-03-01 00:00:00.000
2 2010-03-01 00:00:00.000
3 2010-03-01 00:00:00.000
I want it to be
Rn ColDate
1 2010-02-01 00:00:00.000
1 2010-02-01 00:00:00.000
1 2010-02-01 00:00:00.000
1 2010-02-01 00:00:00.000
1 2010-02-02 00:00:00.000
2 2010-03-01 00:00:00.000
2 2010-03-01 00:00:00.000
2 2010-03-01 00:00:00.000
How could i do that
Thanks
-
With (Base query)
SELECT * FROM cte inner join
(SELECT Date ROW_NUMBER() FROM CTE GROUP BY Date) dta
...
-
thanks but this want solve mu issue
date cane be different for smae month something like
Rn ColDate
1 2010-02-01 00:00:00.000
2 2010-02-03 00:00:00.000
3 2010-02-05 00:00:00.000
4 2010-02-10 00:00:00.000
5 2010-02-12 00:00:00.000
1 2010-03-01 00:00:00.000
2 2010-03-11 00:00:00.000
3 2010-03-21 00:00:00.000
I want o/p AS
Rn ColDate
1 2010-02-01 00:00:00.000
1 2010-02-03 00:00:00.000
1 2010-02-05 00:00:00.000
1 2010-02-10 00:00:00.000
1 2010-02-12 00:00:00.000
2 2010-03-01 00:00:00.000
2 2010-03-11 00:00:00.000
2 2010-03-21 00:00:00.000
-
check out partition by in the row_number topics of books online.
-
Yes I did
ROW_NUMBER() OVER( PARTITION BY DATEPART(yy,date),DATEPART(mm,date) ORDER BY DATEPART(yy,date),DATEPART(mm,date))
but that's not giving me desire result
-
order by date
-
Try using DENSE_RANK instead of ROW_NUMBER
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
I tried that option too
-
DENSE_RANK () is not gives me desire result
-
Sorry I misread the question!!!!!
SET DATEFORMAT YMDDECLARE @tbl table (dt datetime)
insert into @tbl (dt)
SELECT
'2010-02-01' UNION ALL SELECT
'2010-02-03' UNION ALL SELECT
'2010-02-05' UNION ALL SELECT
'2010-02-10' UNION ALL SELECT
'2010-02-12' UNION ALL SELECT
'2010-03-01' UNION ALL SELECT
'2010-03-11' UNION ALL SELECT
'2010-03-21'
SELECT dt, ROW_NUMBER() OVER(PARTITION BY YEAR(dt), MONTH(dt) ORDER BY dt) rn FROM @tbl
ORDER BY dt
dt rn
2010-02-01 1
2010-02-03 2
2010-02-05 3
2010-02-10 4
2010-02-12 5
2010-03-01 1
2010-03-11 2
2010-03-21 3
-
yes thats what I my O/P is
I wat o/p as
dt rn
2010-02-01 1
2010-02-03 1
2010-02-05 1
2010-02-10 1
2010-02-12 1
2010-03-01 2
2010-03-11 2
2010-03-21 2
Thanks
-
yes My o/p is same as this
I want o/p something like this
dt rn
2010-02-01 1
2010-02-03 1
2010-02-05 1
2010-02-10 1
2010-02-12 1
2010-03-01 2
2010-03-11 2
2010-03-21 2
so rn = 1 for forst month and 2 for 2nd month
Thanks
-
SELECT dt, DENSE_RANK() OVER(ORDER BY YEAR(dt), MONTH(dt)) rn FROM @tbl
ORDER BY dt
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
I had actually read it correctly the first time :hehe:
SET DATEFORMAT YMDDECLARE @tbl table (dt datetime)
insert into @tbl (dt)
SELECT
'2010-02-01' UNION ALL SELECT
'2010-02-03' UNION ALL SELECT
'2010-02-05' UNION ALL SELECT
'2010-02-10' UNION ALL SELECT
'2010-02-12' UNION ALL SELECT
'2010-03-01' UNION ALL SELECT
'2010-03-11' UNION ALL SELECT
'2010-03-21'
SELECT t.dt, dt.rn FROM @tbl t INNER JOIN (
SELECT YEAR(dt) y, MONTH(dt) m, ROW_NUMBER() OVER(ORDER BY YEAR(dt), MONTH(dt)) rn FROM @tbl GROUP BY YEAR(dt), MONTH(dt)
) dt ON YEAR(t.dt) = dt.y AND MONTH(t.dt) = dt.m
ORDER BY t.dt
--1 2010-02-01
--1 2010-02-03
--1 2010-02-05
--1 2010-02-10
--1 2010-02-12
--2 2010-03-01
--2 2010-03-11
--2 2010-03-21
--
-
Mark-101232 (5/13/2011)
SELECT dt, DENSE_RANK() OVER(ORDER BY YEAR(dt), MONTH(dt)) rn FROM @tblORDER BY dt
Nice, had never seen that function before!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply