Ranking functions
-
I need a second set of eyes to help with my lunacy/stupidity/senility. I've got a set of interview questions, the categories of the questions, and the interview guide that "packages" them together. I want each question category within an interview guide to be numbered, and each question within each category to be numbered.
Like this:
My existing code:
SELECT
ROW_NUMBER() OVER (PARTITION BY QuestionCategoryID ORDER BY QuestionCategoryID, GuideID, InterviewerID) CategoryNumber --This is not working
,ROW_NUMBER() OVER (PARTITION BY QuestionCategoryID, GuideID, InterviewerID ORDER BY GuideID ,InterviewerID) QuestionNumber
,GuideID
,InterviewerID
,QuestionCategoryID
,QuestionID
,QuestionCategory
,Question
FROM #TestingData
ORDER BY GuideID
,InterviewerID
,QuestionCategoryID
,QuestionIDCode to create the sample data
CREATE TABLE #TestingData
(
GuideID int,
InterviewerID int,
QuestionCategoryID int,
QuestionID int,
QuestionCategory nvarchar(1000),
Question nvarchar(1000)
)
INSERT INTO #TestingData(GuideID, InterviewerID, QuestionCategoryID,QuestionID,QuestionCategory, Question)
VALUES
(1, 1, 50024, 50312, 'D - Personal Att. - Initiating Action', 'Have you generated any new ideas or suggestions while at school? Give me an example.'),
(1, 1, 50024, 50314, 'D - Personal Att. - Initiating Action', 'Have you ever done more than was required in a course? Give me an example.'),
(1, 1, 50024, 50331, 'D - Personal Att. - Initiating Action', 'Describe a situation in which you identified a problem and took action to correct it rather than wait for someone else to do so.'),
(1, 1, 50039, 50390, 'C - Bus./Mgmt. Technical / Professional Knowledge', 'Give me an example of an assignment you worked on that showed your expertise in ________. What did you do?'),
(1, 1, 50039, 50393, 'C - Bus./Mgmt. Technical / Professional Knowledge', 'What technical training have you received? Can you give me an example of how you''ve applied this training?'),
(1, 1, 50039, 50399, 'C - Bus./Mgmt. Technical / Professional Knowledge', 'Give me an example of a project you completed that demonstrated your technical expertise in _________.'),
(1, 1, 50058, 51103, 'C - Bus./Mgmt. - Entrepreneurship', 'Give me an example of a time when you took a business risk with the intent to improve performance of a business or brand. Describe the risks and benefits you considered in arriving at a final recommendation.'),
(1, 1, 50058, 51107, 'C - Bus./Mgmt. - Entrepreneurship', 'Can you give me an example of a new strategy you have implemented that was intended to increase profits?'),
(1, 1, 50058, 51111, 'C - Bus./Mgmt. - Entrepreneurship', 'Give me an example of an instance in which you took a business risk with the intent to improve performance of a business or brand. Describe the risks and benefits you considered in arriving at a final recommendation.'),
(1, 1, 50063, 51163, 'D - Personal Att. - Learning Orientation', 'Describe a technical skill you have recently acquired and how did you go about acquiring this skill?'),
(1, 1, 50063, 51165, 'D - Personal Att. - Learning Orientation', 'It’s important to understand as much as possible about internal partners. Tell me about one of your most important internal partners and how you learned about their needs and priorities.'),
(1, 1, 50063, 51167, 'D - Personal Att. - Learning Orientation', 'It is never easy to learn about your own organization’s new technology, product, or service. Tell me about how you learned about a recent new technology, product, or service in your organization.'),
(1, 1, 50070, 51239, 'A - Interpersonal - Sales Persuasion (EXEC)', 'Give me an example of a time when you were able to gain commitment from a customer to significantly increase their use of your products/services. What did you do?'),
(1, 1, 50070, 51242, 'A - Interpersonal - Sales Persuasion (EXEC)', 'Describe a competitive sales situation in which you were not able to differentiate your product/service. What was your approach and why was it not successful?'),
(1, 1, 50070, 51245, 'A - Interpersonal - Sales Persuasion (EXEC)', 'What types of sales situations have given you the most trouble? Describe a particular challenge and how you handled it.'),
(1, 2, 50024, 50313, 'D - Personal Att. - Initiating Action', 'Have you suggested new ideas to any of your professors? Tell me about one of your suggestions. What prompted the idea? What happened?'),
(1, 2, 50024, 50324, 'D - Personal Att. - Initiating Action', 'What steps have you taken to improve your skills or performance? Give me an example of when you did this.'),
(1, 2, 50024, 50336, 'D - Personal Att. - Initiating Action', 'Often there are no rewards when we volunteer for special projects or assignments. Tell me about a time when you didn''t volunteer or participate in a special project or assignment because there was no reward or recognition for doing so.'),
(1, 2, 50039, 50391, 'C - Bus./Mgmt. Technical / Professional Knowledge', 'Sometimes complex projects require additional expertise. Describe a situation in which you had to request help on one of your projects or assignments.'),
(1, 2, 50039, 50394, 'C - Bus./Mgmt. Technical / Professional Knowledge', 'What experience do you have operating computers (or other office equipment)? How proficient are you?'),
(1, 2, 50039, 50402, 'C - Bus./Mgmt. Technical / Professional Knowledge', 'How much experience have you had operating a _________ (typewriter, word processor, drill press, etc.)? How proficient are you?'),
(1, 2, 50058, 51104, 'C - Bus./Mgmt. - Entrepreneurship', 'Tell me about a time when you have used market data to formulate a new business plan. How did you identify the specific activities necessary to execute the plan?'),
(1, 2, 50058, 51108, 'C - Bus./Mgmt. - Entrepreneurship', 'Have you ever been involved in an effort to advance business in a particular market? How did you approach the task? What were the results?'),
(1, 2, 50058, 51112, 'C - Bus./Mgmt. - Entrepreneurship', 'Tell me about a time when you have used market and/or competitive data to formulate a business plan. How did you identify the specific activities necessary to execute the plan?'),
(1, 2, 50063, 51164, 'D - Personal Att. - Learning Orientation', 'Tell me about a time when you had to quickly learn and understand a competitor’s product line, culture, or strategy. How did you go about this?'),
(1, 2, 50063, 51166, 'D - Personal Att. - Learning Orientation', 'It’s important to understand as much as possible about your customers. Tell me about one of your most important customers and how you learned about their needs and priorities.'),
(1, 2, 50063, 51168, 'D - Personal Att. - Learning Orientation', 'In your field, it is probably important to stay abreast of changes in the industry and marketplace. What actions have you taken to stay informed?'),
(1, 2, 50070, 51240, 'A - Interpersonal - Sales Persuasion (EXEC)', 'Describe a competitive situation in which you won the sale because of how you positioned your offering (product/service.) What was your approach?'),
(1, 2, 50070, 51243, 'A - Interpersonal - Sales Persuasion (EXEC)', 'Tell me about your toughest sales experience. Why was it so challenging and how did you respond?'),
(1, 2, 50070, 51246, 'A - Interpersonal - Sales Persuasion (EXEC)', 'Give me an example of the most unusual approach you have used in order to make a sale.'),
(1, 3, 50051, 51021, 'C - Bus./Mgmt. - Global Acumen', 'It is important to understand as much as possible about key economic, social, and political trends throughout the world. Tell me about a specific situation when you needed to learn about these trends in relation to a business decision and how did you go about doing so?'),
(1, 3, 50051, 51022, 'C - Bus./Mgmt. - Global Acumen', 'It is impossible to always anticipate the potential implications of your department/team’s actions on other parts of the organization. Tell me about a time when your organization took action that adversely affected another part of your organization from a cultural or global perspective.'),
(1, 3, 50051, 51023, 'C - Bus./Mgmt. - Global Acumen', 'Tell me about a time when you have used global market and/or financial data to formulate a business plan. How did you identify the specific activities necessary to execute the plan?'),
(1, 3, 50058, 51105, 'C - Bus./Mgmt. - Entrepreneurship', 'Can you give me an example of an innovative idea or concept you have conceived that really had an impact on a business?'),
(1, 3, 50058, 51109, 'C - Bus./Mgmt. - Entrepreneurship', 'Can you think of a time when you identified a market trend that enabled you to enhance your competitive advantage? How did you recognize the trend? What actions did you take?'),
(1, 3, 50058, 51113, 'C - Bus./Mgmt. - Entrepreneurship', 'Can you give me an example of an innovative idea or concept you have conceived that really had an impact on business?'),
(1, 3, 50070, 51241, 'A - Interpersonal - Sales Persuasion (EXEC)', 'We don’t always win every bid or sale that we expect to. Describe a situation when this happened to you. What went wrong?'),
(1, 3, 50070, 51244, 'A - Interpersonal - Sales Persuasion (EXEC)', 'Sometimes it can be difficult to meet all of a customer''s needs. Can you describe a time when it difficult for you to uncover and address a customer''s needs?'),
(1, 3, 50070, 51247, 'A - Interpersonal - Sales Persuasion (EXEC)', 'Describe your approach with new prospects. Give me an example of a time when this approach was particularly successful.'),
(1, 3, 50056, 51074, 'A -Interpersonal - Developing Strategic Relationships (EXEC)', 'Tell me about a situation in which you became frustrated or impatient when dealing with a business partner. How did you handle that?'),
(1, 3, 50056, 51076, 'A -Interpersonal - Developing Strategic Relationships (EXEC)', 'Tell me about a time that you wanted to make a major change on a project and you had to get an internal/external business partner to agree to it.'),
(1, 3, 50056, 51079, 'A -Interpersonal - Developing Strategic Relationships (EXEC)', 'Describe a time when a work group (other than yours) did something wrong and caused a problem for your group. How did you respond?')
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ -
Maybe this instead:
SELECT
DENSE_RANK() OVER (ORDER BY QuestionCategoryID) CategoryNumber,
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
Yeah. The coffee kicked in. This worked. Thanks!
SELECT
DENSE_RANK() OVER (PARTITION BY GuideID, InterviewerID ORDER BY QuestionCategoryID, GuideID, InterviewerID) CategoryNumber
,ROW_NUMBER() OVER (PARTITION BY QuestionCategoryID, GuideID, InterviewerID ORDER BY GuideID ,InterviewerID) QuestionNumber
,GuideID
,InterviewerID
,QuestionCategoryID
,QuestionID
,QuestionCategory
,Question
FROM #TestingData
ORDER BY GuideID
,InterviewerID
,QuestionCategoryID
,QuestionIDMichael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply