November 19, 2010 at 8:09 am
Hi,
I have a table that I contains 3 column that I need to 'group by' in order to populate a counter on a 4th column. An additional date field can be used to 'order by' ColDate - using DESC.
Example
Col1 Col2 Col3
NWr 123 DDO
NWr 123 DDO
NWr 123 DDO
NWr 123 DDS
NWr 123 DDS
TRe 123 DDO
becomes
Col1 Col2 Col3 Col4
NWr 123 DDO 1
NWr 123 DDO 2
NWr 123 DDO 3
NWr 123 DDS 1
NWr 123 DDS 2
TRe 123 DDO 1
I thought it was something like the following:
;
WITH cteRank AS (
SELECT Col4,
ROW_NUMBER() OVER (
PARTITION BY Col1, Col2, Col2
ORDER BY ColDate DESC) AS Calculated_Rank1
FROM @tblResults
)
UPDATE cteRank SET Col4 = Calculated_Rank1
But this just produces additional rows.
Any ideas please?
Thanks in advance.
November 19, 2010 at 8:56 am
I'm not sure if it's a typo or an actual error, but you partition by col2 twice in your CTE.
I'm also not sure what you mean about an update producing extra rows. Update can't add rows to a table.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 19, 2010 at 9:51 am
I think NTILE could fix things. Try the following please.
;
WITH cteRank AS (
SELECT Col4,
NTILE() OVER ( PARTITION BY Col1, Col2, Col2
ORDER BY ColDate DESC) AS Calculated_Rank1
FROM @tblResults
)
UPDATE cteRank SET Col4 = Calculated_Rank1
Cheers
November 19, 2010 at 10:21 am
Kindly try the following. In future, try putting sample script will help you get the answer back more quickly. thanks.
create table #tmp1
(
col1 varchar(4),
col2 varchar(4),
col3 varchar(4),
col4 varchar(4),
ColDate datetime default getdate()
)
INSERT INTO #tmp1 (Col1, Col2, Col3, Coldate)
SELECT 'NWr','123','DDO', '01-Jan-2010'
UNION ALL
SELECT 'NWr','123','DDO ', '01-Feb-2010'
UNION ALL
SELECT 'NWr','123','DDO', '01-Mar-2010'
UNION ALL
SELECT 'NWr','123','DDS', '01-Jan-2010'
UNION ALL
SELECT 'NWr','123','DDS', '01-Feb-2010'
UNION ALL
SELECT 'TRe','123','DDO', '01-Jan-2010'
select * from #tmp1
;
WITH cteRank AS (
SELECT Col4,
NTILE(4) OVER (
PARTITION BY Col1, Col2, Col3
ORDER BY ColDate) AS Calculated_Rank1
FROM #tmp1
)
UPDATE cteRank SET Col4 = Calculated_Rank1
select * from #tmp1 order by Col1, Col2, Col3, col4
DROP TABLE #tmp1
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply