Need assistance with a list and putting a value in a virtual column

Question

Need assistance with a list and putting a value in a virtual column

Stephen crocker

SSCrazy

Points: 2512
More actions
December 16, 2010 at 12:53 pm

#72952

I have a list that produces the following (sample data)
DetailName VoteCount
Test1 325
Test2 101
Test3 502
Test4 99
Test5 420
I have the query working that will produce the above list. I'm trying to figure out how I can look in the second column of the query and determine with a column (virtual Column) the to 3 numbers.
The query looks like this
SELECT detailname, SUM(votecount) FROM Table1
GROUP BY detail Name
I need to create a report for this and what I want to do is basically flag (ReportBuilder2 or BIDS) the top 3 people in the list, I don't want to isoloate those 3 I need to show all of the users in the list.
Could someone help me out with this please?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

I don't understand your requirement.

Please post table def, sample data and expected result in a ready to use format as described in the first link in my signature.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Stephen crocker SSCrazy Points: 2512 More actions · Answer 2

What I need it something like this

DetailName VoteCount (Winner) this is a virtual column

test1 300 0

test2 400 1

test3 500 1

test4 340 0

test5 498 1

I need to flag the top 3 in the votecount with a number 1 so in the report I can Flag those a winners

Does this make since?

LutzM SSC Guru Points: 107049 More actions · Answer 3

Use a subquery (or CTE) to select the TOP 3 order VoteCount and join that back to your original table on Detailname in an update statement.

As a side note: ready to use sample data will lead to tested and ready to use code. Verbal description and EXCEL data I'll only answer with verbal description, too... 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Stephen crocker SSCrazy Points: 2512 More actions · Answer 4

Stephen crocker

SSCrazy

Points: 2512

December 16, 2010 at 1:43 pm

#1263706

Thank you, I'll give that a try

WayneS SSC Guru Points: 95460 More actions · Answer 5

Stephen crocker (12/16/2010)
Thank you, I'll give that a try

I take this to mean that you're going to read that link he suggested you read, and then to post your data in the manner the link talks about so that you'll get tested code back to solve your problem?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2