Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Friday, August 16, 2013 9:38 PM
This is my query:
select
market,
score,
weeknum,
ROW_NUMBER() OVER (partition by weeknum ORDER BY weeknum, score asc) AS [OurRank]
from MyTable
This query returns the following, but I need to calculate the column [NewCol] which is max([OurRank]) minus [OurRank] per week. In the following case, max([OurRank]) for week1 is 4 while max([OurRank]) for week2 is 6 since it's based on the number of markets :
Market Score Weeknum OurRank NewCol
Market1 2.40 1 1 3
Market3 2.50 1 2 2
Market4 2.70 1 3 1
Market2 2.86 1 4 0
Market3 2.30 2 1 5
Market5 2.40 2 2 4
Market2 2.50 2 3 3
Market4 2.66 2 4 2
Market1 2.76 2 5 1
Market6 2.86 2 6 0
Any help is aprpeciated.
VM
Saturday, August 17, 2013 12:37 AM ✅Answered
Since you are using ROW_NUMBER() function, you could calculate that number of rows per partition and use it to calculate the new column using:
...
COUNT(*) OVER(PARTITION BY weeknum) -
ROW_NUMBER() OVER (PARTITION BY weeknum ORDER BY weeknum, score asc) AS NewCol
...
AMB
Some guidelines for posting questions...
Saturday, August 17, 2013 1:12 AM
>>This is my query: <<
Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules (you have no idea). Temporal data should use ISO-8601 formats (you fail again). Code should be in Standard SQL as much as possible and not local dialect.
This is minimal polite behavior on SQL forums. Trying to guess what a polite poster who follows Netiquette would have posted, you have a mess.
There is an ISO-8601 week date that looks like “yyyyWww-d” where is YYYY is the year, ww is 01-53 for the week within year, and d is 1-7 for the day of the week. This standard is one of the foundations of IT, not just SQL.
Here is my guess, without any help from you:
CREATE TABLE Something_Markets
(market_name VARCHAR (15) NOT NULL,
week_date CHAR(10) NOT NULL
CHECK (week_date LIKE '[12][0-9][0-9][0-9]W[0-5][0-9]'),
PRIMARY KEY (market_name, week_date),
foobar_score DECIMAL (5,2) NOT NULL
CHECK (foobar_score BETWEEN 0.00 AND 5.99)
);
INSERT INTO Something_Markets (market_name, week_date, foobar_score)
VALUES
('Market1', '2013W01', 2.40),
('Market1', '2013W02', 2.76),
('Market2', '2013W02', 2.50),
('Market2', '2013W01', 2.86),
('Market3', '2013W02', 2.30),
('Market4', '2013W02', 2.66),
('Market4', '2013W01', 2.70),
('Market5', '2013W02', 2.40),
('Market6', '2013W02', 2.86);
Did I guess right? If you want the max score per week, why not use MAX()
;WITH X (market_name, foobar_score, week_date, foobar_score_max)
AS (SELECT market_name, foobar_score, week_date,
MAX(foobar_score) OVER (PARTITION BY week_date)
FROM Something_Markets)
SELECT market_name, foobar_score, week_date
FROM X
WHERE foobar_score_max = foobar_score;
========================
Market2 2.86 2013W01
Market6 2.86 2013W02
--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
Saturday, August 17, 2013 11:02 AM
Refer the below query,
CREATE TABLE [dbo].[mytable]( [Market] [varchar](10) NULL, [Score] [decimal](5, 2) NULL, [Weeknum] [int] NULL, [OurRank] [int] NULL) ON [PRIMARY]
;with cte as ( select market, score, weeknum, ROW_NUMBER() OVER (partition by weeknum ORDER BY weeknum, score asc) AS [OurRank] from MyTable )select market,score,a.weeknum,a.OurRank,(b.ourrank - a.ourrank) NewColfrom cte a inner join (select weeknum,max(ourrank) ourrank from cte group by weeknum) b on a.weeknum=b.weeknum
Regards, RSingh