Give Credits to Top 5 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Give Credits to Top 5

Author

Topic

damdeok
Starting Member

39 Posts

Posted - 2010-10-28 : 03:42:27

I have 2 tables: M_Credits and Vote_Ranking

M_Credits has 2 columns:
- MemberID
- Credits

Vote_Ranking has 2 columns:
- AccountID
- Votes

To get the Top 5 in Vote_Ranking I have this code:

select TOP 5 votes, AccountID from vote_ranking order by votes desc

I want to give credits to those Top 5. I've tried this code but it did not work:

UPDATE M_Credits SET credits=credits+200
where MemberID in (select TOP 5 votes, AccountID from vote_ranking)

Please help.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-28 : 03:52:37

quote:

UPDATE M_Credits SET credits=credits+200
where MemberID in (select TOP 5 votes, AccountID from vote_ranking order by votes desc)

Please help.

I am assuming that M_Credits.MemberID = vote_ranking.AccountID

If your requirement is something else then post sample data and expected output.

damdeok
Starting Member

39 Posts

Posted - 2010-10-28 : 03:57:31

Yes. M_Credits.MemberID = vote_ranking.AccountID

I tried the code you corrected.

I got this error:

quote:
Cannot resolve the collation conflict between "Chinese_PRC_CI_AS" and "Modern_Spanish_CI_AS" in the equal to operation.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-28 : 05:49:35

quote:
Originally posted by damdeok

Yes. M_Credits.MemberID = vote_ranking.AccountID

I tried the code you corrected.

I got this error:

quote:
Cannot resolve the collation conflict between "Chinese_PRC_CI_AS" and "Modern_Spanish_CI_AS" in the equal to operation.

Give the DDL script of both the tables so that we don't have to guess the collation.

damdeok
Starting Member

39 Posts

Posted - 2010-10-28 : 06:47:13

I used this code and it works. Thanks.

UPDATE M_Credits SET credits=credits+200
where MemberID in (select TOP 5 AccountID COLLATE Chinese_PRC_CI_AS from vote_ranking order by votes desc)

If I may ask, I want to give different credits to each Top 5 like:

Top 1 = 200 credits
Top 2 = 150 credits
Top 3 = 100 credits
Top 4 = 75 credits
Top 5 = 50 credits

Can you please give me a code that will do this? Thanks.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-28 : 07:48:37

I don't have facility to compile and check the code but the below code should give you some idea.

UPDATE M_Credits SET credits= credits +
Case when ST.Srno = 1 then 200
When St.Srno =2 then 150
--and so on
end

From M_Credits M Inner join
(Select top 5 Row_number() Over (Order by Votes Desc) As Srno, AccountID from vote_ranking ) As ST
On M.MemberID = St.AccountID

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

damdeok
Starting Member

39 Posts

Posted - 2010-10-28 : 11:28:09

Using this code:

UPDATE M_CREDITS SET credits= credits + Case
When St.Srno = 1 then 200
When St.Srno = 2 then 150
When St.Srno = 3 then 100
When St.Srno = 4 then 75
When St.Srno = 5 then 50
end
From M_CREDITS M Inner join
(Select top 5 Row_number() Over (Order by Votes Desc) As Srno, AccountID collate Chinese_PRC_CI_AS from vote_ranking) As ST
On M.MemberID = St.AccountID

gives me an error:

quote:
Msg 8155, Level 16, State 2, Line 1
No column was specified for column 2 of 'ST'.
Msg 207, Level 16, State 1, Line 10
Invalid column name 'AccountID'.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-28 : 12:37:37

Try:

UPDATE M_CREDITS SET credits= credits +
Case
When St.Srno = 1 then 200
When St.Srno = 2 then 150
When St.Srno = 3 then 100
When St.Srno = 4 then 75
When St.Srno = 5 then 50
end
From M_CREDITS M Inner join
(Select top 5 Row_number() Over (Order by Votes Desc) As Srno, AccountID collate Chinese_PRC_CI_AS as AccountID from vote_ranking) As ST
On M.MemberID = St.AccountID

damdeok
Starting Member

39 Posts

Posted - 2010-10-29 : 02:52:21

It works. Thanks.

Also, in Vote_Ranking table I have a separate vote column for each month.

vote1 for January
vote2 for February
vote3 for March
...and so on.

When the date is February 1, I will give credits to top 5 of January from vote1 column.

With your code and sql agent, I can make 12 jobs with different schedules to get it done.

Any work around that if the day is February 1 it will give credits to top 5 of vote1?

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-29 : 06:37:43

What i understand is that on 1st of any month, you want to give credit to top 5 of previous month.
If my understanding is correct then you don't require 12 jobs for it. A single job is enough.

damdeok
Starting Member

39 Posts

Posted - 2010-10-29 : 06:59:50

Your understanding is correct.

Problem with 1 job is our code only read 1 vote column and I have 12 of those.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-29 : 09:16:24

quote:
Originally posted by damdeok

Your understanding is correct.

Problem with 1 job is our code only read 1 vote column and I have 12 of those.

Can you post the DDL along with some sample data and expected output.

damdeok
Starting Member

39 Posts

Posted - 2010-10-29 : 09:47:17

Vote_Ranking table

On Feb 1 I will give credits to top 5 of votes1.
On Mar 1 I will give credits to top 5 of votes2.
On Apr 1 I will give credits to top 5 of votes3.
...
On Dec 1 I will give credits to top 5 of votes11.
On Jan 1 I will give credits to top 5 of votes12.

damdeok
Starting Member

39 Posts

Posted - 2010-11-01 : 02:22:57

Too bad. I'm testing on SQL 2005 and I'm using SQL 2000 for my live database.

I got this error in SQL 2000 using our code:

quote:
'Row_number' is not a recognized function name.

damdeok
Starting Member

39 Posts

Posted - 2010-11-10 : 04:35:04

/bump for answer

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-10 : 05:04:56

In SQL Server 2000 there is no ROW_NUMBER().
You can use this workaround instead:

SELECT TOP 5
 IDENTITY(INT,1,1) AS Srno,
 AccountId
    INTO #ST
FROM vote_ranking
ORDER BY Votes DESC

UPDATE M_CREDITS SET credits= credits + 
CASE
 WHEN St.Srno = 1 THEN 200
 WHEN St.Srno = 2 THEN 150
 WHEN St.Srno = 3 THEN 100
 WHEN St.Srno = 4 THEN 75
 WHEN St.Srno = 5 THEN 50
END
FROM M_CREDITS M Inner join
#ST as St ON M.MemberID = St.AccountID


DROP TABLE #ST

No, you're never too old to Yak'n'Roll if you're too young to die.

damdeok
Starting Member

39 Posts

Posted - 2010-11-10 : 12:32:29

Thanks for your reply. I'm getting this error:

quote:
Msg 468, Level 16, State 9, Line 8
Cannot resolve the collation conflict between "Chinese_PRC_CI_AS" and "Modern_Spanish_CI_AS" in the equal to operation.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-11-11 : 00:50:56

quote:
Originally posted by damdeok

Thanks for your reply. I'm getting this error:

quote:
Msg 468, Level 16, State 9, Line 8
Cannot resolve the collation conflict between "Chinese_PRC_CI_AS" and "Modern_Spanish_CI_AS" in the equal to operation.

Try It:

Note: I have not tested the code.

SELECT TOP 5
IDENTITY(INT,1,1) AS Srno,
AccountId collate Chinese_PRC_CI_AS as AccountID
INTO #ST
FROM vote_ranking
ORDER BY Votes DESC

UPDATE M_CREDITS SET credits= credits +
CASE
WHEN St.Srno = 1 THEN 200
WHEN St.Srno = 2 THEN 150
WHEN St.Srno = 3 THEN 100
WHEN St.Srno = 4 THEN 75
WHEN St.Srno = 5 THEN 50
END
FROM M_CREDITS M Inner join
#ST as St ON M.MemberID = St.AccountID

DROP TABLE #ST

damdeok
Starting Member

39 Posts

Posted - 2010-11-11 : 01:31:54

First execution error:

quote:
(5 row(s) affected)
Msg 468, Level 16, State 9, Line 8
Cannot resolve the collation conflict between "Chinese_PRC_CI_AS" and "Modern_Spanish_CI_AS" in the equal to operation.

Second execution error:

quote:
Msg 2714, Level 16, State 6, Line 1
There is already an object named '#ST' in the database.

Thanks for your reply.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-11-11 : 02:20:52

Either you drop the temp table before trying the solution using

Drop table #ST

Or

Try the solution in new window.

damdeok
Starting Member

39 Posts

Posted - 2010-11-11 : 02:33:30

I have dropped #ST table. This is the error when I execute the code.

quote:
(5 row(s) affected)
Msg 468, Level 16, State 9, Line 8
Cannot resolve the collation conflict between "Chinese_PRC_CI_AS" and "Modern_Spanish_CI_AS" in the equal to operation.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources