INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Need a little help on how to handle a rank query

Need a little help on how to handle a rank query

(OP)
I started to do an update query like:

update comprank set ranking = '1 out of 1' where measureid = '234' and prodabbrev = 'hmo' and rank = 1 and market = 'ca;

but then I realized that is not going to work based on the raw data I have. Here is an example of the data and I also have 3924 total rows and 36 different measureids, 2 different prodabbrev, and 14 different markets. Here is the data like it is in the table, just a snippet. Well it does not paste right so going to attach the excel

RE: Need a little help on how to handle a rank query

1. Attachment missing.
2. I don't see a connection to RANK queries, most probably it doesn't have to do with RANK() T-SQL.

Otherwise your goal is still totally in the dark.

Bye, Olaf.

RE: Need a little help on how to handle a rank query

(OP)
Thanks. The attachment not sure why it did not work but I will just have to build it like case statements. No other way I have found to do it. Someone gave me this but it does not work in SQL R2008. I have 36 measureids in all with 14 markets and 2 prodabbrevs. So trying to figure out how to use some sort of switch otherwise I will have to case statement it

update
comprank set ranking = switch (measureID=12,'5 out of 10',measureID=24,'4 out of 10',etc for all measureids) where market
='ca'and prodabbrev ='hmo' and competitor ='California -HMO/POS';
update
comprank set ranking = switch (measureID=12,'5 out of 10',measureID=24,'4 out of 10',etc for all measureids) where market
='ct'and prodabbrev ='hmo' and competitor ='Connecticut -HMO/POS';
update
comprank set ranking = switch (measureID=12,'5 out of 10',measureID=24,'4 out of 10',etc for all measureids) where market
='me'and prodabbrev ='hmo' and competitor ='Maine -HMO/POS';

RE: Need a little help on how to handle a rank query

If you want what I think you want.... this seems doable, but before I give you advice, I would really need to see some sample data and expected results.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

RE: Need a little help on how to handle a rank query

If would (probably) help if you would state:

This is what I currently have in my table:

comprank [table]
ranking   measureid   prodabbrev  rank  market 
  ???       ???          ???       ???    ???
  ???       ???          ???       ???    ???
  ???       ???          ???       ???    ???
  ...
 
And this is what I need to have after my update statements:

comprank [table]
ranking   measureid   prodabbrev  rank  market 
  ???       ???          ???       ???    ???
  ???       ???          ???       ???    ???
  ???       ???          ???       ???    ???
  ...
 
Based on these rules:
1...
2...
3...
Present the data that is representative of your issue(s)

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Need a little help on how to handle a rank query

I second this, please retry to post your data and expected result. You can try attaching something again, every post can have an attachment, not only the inital one, you can also edit your post for one day.

Anyway, you don't need many updates with several conditions and switch isn't working in SQL you'll need CASE. But don't get this route, you have similar updates differing in the data about conditions, well, that screams out load to be data itself. You have a database here and it's good with data, instead of writing tons of code, put the conditions as data.

To demonstrate the priciple:

CODE

declare @persons as table (age int, agecategory varchar(20));
insert into @persons (age) values (1),(5),(47),(15),(89),(1),(8),(17);

declare @agecategories as table (minage int, maxage int, agecategory varchar(20));
insert into @agecategories values (0,1,'baby'),(2,12,'kid'),(13,19,'teenager'),(20,65,'adult'),(65,1000,'senior');

update @persons set agecategory = agecat.agecategory from @persons as pers inner join @agecategories as agecat on pers.age between agecat.minage and agecat.maxage

Select * from @persons 

@persons here compares to the table you want to update differently under different conditions, and the conditions are given as @agecategories defining age ranges for which certain agecategory names are valid, from baby to senior. In your case that would be a table holding the different combinations of market, competitor and measureid, most probably you have this in a table already anyway, otherwise you see how you can build a table variable or you could make this a permanent table of your database, because maybe you want to repeat this with differing editable conditions.

The whole concept is simply a correlated update. The one table acts as a template list. Instead of a table you might also have two functions for N and M determining the two values of rankinng N out of M. M may be a constant or a count of some records. Anything goes, but don't write out several hardcoded and very similar updates, if you can simply turn all of them to one update taking its conditions and target values as data. Common, you have to think the database way, if you use a database. This data merely is a parameterization and generalisation of otherwise same code, this is not only what data is all about, this is also what programming is all about, you generalize things, parameterize and then can do things repeatedly and fast, just changing the outset by configuration, template or most generally speaking meta data.

If you write out all the different updates you could also manually enter all the rankinngs into your table, what amount of work could you really save that way? Be lazy in doing cumbersome things all over again by thinking of the code to do it for you just concentrating on the core and varying data about the doing, control data, meta data, or even just a function to compute that meta data. That's what it's all about.

Bye, Olaf.

RE: Need a little help on how to handle a rank query

Thank you dudes, I learned many helpful things.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close