April 13, 2017 at 11:54 pm
Comments posted to this topic are about the item Window Function defaults
April 14, 2017 at 7:06 am
Not sure if it is a lack of coffee or not but I am 99.99% certain I chose the correct answer. In my mind I certainly did but perhaps I clicked the wrong one. I have always wished the QOTD page would show your select in addition to the correct answer. Good question Steve.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 14, 2017 at 7:58 am
I know what the correct answer is according to BOL... I just happen to disagree with it because functions like MAX won't be limited to just the preceding and current rows.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 14, 2017 at 11:54 am
I had to stop a second because the partition size is the set of rows for a given team while the Rows/Range clause determines the frame size which is used to calculate the value for a specific row and is a subset of the partition.
There are no facts, only interpretations.
Friedrich Nietzsche
April 17, 2017 at 10:49 am
barry.mcconnell - Friday, April 14, 2017 11:54 AMI had to stop a second because the partition size is the set of rows for a given team while the Rows/Range clause determines the frame size which is used to calculate the value for a specific row and is a subset of the partition.
Same here, I had to think a little longer because of that. The available answers helped redirect the train of thought on this one.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 17, 2017 at 1:33 pm
Jeff Moden - Friday, April 14, 2017 7:58 AMI know what the correct answer is according to BOL... I just happen to disagree with it because functions like MAX won't be limited to just the preceding and current rows.
You can disagree all you want, but the MAX is limited as soon as you add an ORDER BY clause and that limit defaults to the answer marked as correct in the question.
Drew
Edited to remove answer to question.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 17, 2017 at 3:06 pm
Apparently this should have been a two-point question.
Thanks, Steve!
April 17, 2017 at 3:37 pm
drew.allen - Monday, April 17, 2017 1:33 PMJeff Moden - Friday, April 14, 2017 7:58 AMI know what the correct answer is according to BOL... I just happen to disagree with it because functions like MAX won't be limited to just the preceding and current rows.You can disagree all you want, but the MAX is limited as soon as you add an ORDER BY clause and that limit defaults to the answer marked as correct in the question.
Drew
Edited to remove answer to question.
Ok... so explain how that might work if the current row isn't the max row. I still think that BOL has explained it incorrectly.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 17, 2017 at 5:32 pm
BOL is probably shite. Test1, Test2 show different maxes, based on ordering.
CREATE TABLE counters
( countid INT IDENTITY(1,1)
, countername VARCHAR(20)
, counteryear INT
, mycounter INT
)
GO
INSERT dbo.counters
( countername
, counteryear
, mycounter
)
VALUES
( 'Test1', 2012, 1 ),
( 'Test1', 2013, 2 ),
( 'Test1', 2014, 3 ),
( 'Test1', 2015, 4 ),
( 'Test1', 2016, 5 ),
( 'Test1', 2017, 6 )
GO
INSERT dbo.counters
( countername
, counteryear
, mycounter
)
VALUES
( 'Test2', 2012, 1 ),
( 'Test2', 2013, 4 ),
( 'Test2', 2014, 2 ),
( 'Test2', 2015, 8 ),
( 'Test2', 2016, 5 ),
( 'Test1', 2017, 11 )
GO
SELECT
countid,
countername,
counteryear,
mycounter,
sumofallvalues = SUM(mycounter) OVER (ORDER BY counteryear),
maxcounter = MAX(mycounter) OVER (PARTITION BY countername
ORDER BY counteryear
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
)
FROM dbo.counters
ORDER BY
countername,
counteryear;
April 17, 2017 at 5:37 pm
Steve Jones - SSC Editor - Monday, April 17, 2017 5:32 PMBOL is probably shite. Test1, Test2 show different maxes, based on ordering.
CREATE TABLE counters
( countid INT IDENTITY(1,1)
, countername VARCHAR(20)
, counteryear INT
, mycounter INT
)
GO
INSERT dbo.counters
( countername
, counteryear
, mycounter
)
VALUES
( 'Test1', 2012, 1 ),
( 'Test1', 2013, 2 ),
( 'Test1', 2014, 3 ),
( 'Test1', 2015, 4 ),
( 'Test1', 2016, 5 ),
( 'Test1', 2017, 6 )
GO
INSERT dbo.counters
( countername
, counteryear
, mycounter
)
VALUES
( 'Test2', 2012, 1 ),
( 'Test2', 2013, 4 ),
( 'Test2', 2014, 2 ),
( 'Test2', 2015, 8 ),
( 'Test2', 2016, 5 ),
( 'Test1', 2017, 11 )
GO
SELECT
countid,
countername,
counteryear,
mycounter,
sumofallvalues = SUM(mycounter) OVER (ORDER BY counteryear),
maxcounter = MAX(mycounter) OVER (PARTITION BY countername
ORDER BY counteryear
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
)
FROM dbo.counters
ORDER BY
countername,
counteryear;
Steve... are you trying to say that I should not trust BoL?
If not, what should be done about it? (Dead serious, although I do realize that you can be dead or serious.)
April 17, 2017 at 9:34 pm
Revenant - Monday, April 17, 2017 5:37 PMSteve... are you trying to say that I should not trust BoL?
If not, what should be done about it? (Dead serious, although I do realize that you can be dead or serious.)
Or seriously dead. 😉
BoL reminds me of something that former President Ronald Reagan learned... "Trust but verify".
--Jeff Moden
Change is inevitable... Change for the better is not.
April 18, 2017 at 7:03 am
Steve Jones - SSC Editor - Monday, April 17, 2017 5:32 PMBOL is probably shite. Test1, Test2 show different maxes, based on ordering.
CREATE TABLE counters
( countid INT IDENTITY(1,1)
, countername VARCHAR(20)
, counteryear INT
, mycounter INT
)
GO
INSERT dbo.counters
( countername
, counteryear
, mycounter
)
VALUES
( 'Test1', 2012, 1 ),
( 'Test1', 2013, 2 ),
( 'Test1', 2014, 3 ),
( 'Test1', 2015, 4 ),
( 'Test1', 2016, 5 ),
( 'Test1', 2017, 6 )
GO
INSERT dbo.counters
( countername
, counteryear
, mycounter
)
VALUES
( 'Test2', 2012, 1 ),
( 'Test2', 2013, 4 ),
( 'Test2', 2014, 2 ),
( 'Test2', 2015, 8 ),
( 'Test2', 2016, 5 ),
( 'Test1', 2017, 11 )
GO
SELECT
countid,
countername,
counteryear,
mycounter,
sumofallvalues = SUM(mycounter) OVER (ORDER BY counteryear),
maxcounter = MAX(mycounter) OVER (PARTITION BY countername
ORDER BY counteryear
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
)
FROM dbo.counters
ORDER BY
countername,
counteryear;
I'm missing something. Since the partition is on countername, each countername group SHOULD have different max values. I've run your example using several different framings and received the correct answer in every case. Try RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING and you'll get the highest value of the entire set of that countername on each individual row, regardless of which row it is and regardless of the ORDER BY clause. The ORDER BY only effects the frame if you are looking at specific rows such as in your example. Replace the ORDER BY counteryear with ORDER BY (SELECT NULL) and take out the final ordering of the results and look at the data carefully. It gives you the maximum value between the two rows in question.
There are no facts, only interpretations.
Friedrich Nietzsche
April 18, 2017 at 7:09 am
Here are some examples I used to finally get my head around framing:if object_id('tempdb..#DinnerParties') is not null
begin
drop table #DinnerParties
end
GO
CREATE TABLE #DinnerParties(DinerFirstName varchar(20)
,DinerLastName varchar(20)
,PartyName varchar(20)
,ArrivalTime time);
GO
INSERT INTO #DinnerParties VALUES
('Lala','Avis','Giltner','18:27:00'),
('Althea','Avis','Giltner','18:29:00'),
('Shanti','Giltner','Giltner','18:31:00'),
('Andre','Giltner','Giltner','18:37:00'),
('Malvina','Hamois','Overby','18:01:00'),
('Luvenia','Malm','Overby','18:01:00'),
('Hamlette','Overby','Overby','18:01:00'),
('Kindra','Overby','Overby','18:01:00'),
('Milford','Overby','Overby','18:01:00'),
('Giuseppe','Pellegrin','Pellegrin','18:38:00'),
('Mary','Pellegrin','Pellegrin','18:38:00'),
('Kiyoko','Zeman','Pellegrin','18:42:00'),
('Geneva','Oberlin','Price','19:00:00'),
('Lenita','Price','Price','19:05:00'),
('Matilda','Villeneuve','Price','19:05:00'),
('Jewell','Sprowl','Sprowl','19:25:00'),
('Tasha','Sturtavant','Sprowl','19:25:00');
GO
SELECT *
,COUNT(*) OVER( PARTITION BY PartyName
ORDER BY ArrivalTime ASC) AS [NumberArrived] -- Default framing is being used, everybody arrived together
FROM #DinnerParties
WHERE PartyName = 'Overby';
SELECT *
,COUNT(*) OVER( PARTITION BY PartyName
ORDER BY ArrivalTime ASC) AS [NumberArrived] -- Default framing but not everyone arrived together; counting is by VALUE of ORDER BY
FROM #DinnerParties
WHERE PartyName = 'Price';
SELECT *
,COUNT(*) OVER( PARTITION BY PartyName
ORDER BY ArrivalTime ASC, DinerFirstName ASC ) AS [NumberArrived] -- Default framing but forcing every row to be unique in the ORDER BY
FROM #DinnerParties
WHERE PartyName = 'Price';
SELECT *
,COUNT(*) OVER( PARTITION BY PartyName
ORDER BY ArrivalTime ASC
ROWS UNBOUNDED PRECEDING) AS [NumberArrived] -- Using row position for framing, shortcut syntax is Current Row and everybody behind Current Row based on ORDER BY
FROM #DinnerParties;
There are no facts, only interpretations.
Friedrich Nietzsche
April 18, 2017 at 8:24 am
It's less a trust issue, and more sometimes a poor wording issue that is ambiguous. I'm sure some people take the docs and it makes perfect sense, but at other times it doesn't.
I think over time, I've learned to read them well, but I think that's a voice of experience, and less a voice of actually taking the English words as they might otherwise be interpreted. I don't have a good solution. I see the issue with questions here constantly, that what I think is a clear wording, and some agree, others view as ambiguous.
April 18, 2017 at 1:02 pm
Jeff Moden - Monday, April 17, 2017 3:37 PMdrew.allen - Monday, April 17, 2017 1:33 PMJeff Moden - Friday, April 14, 2017 7:58 AMI know what the correct answer is according to BOL... I just happen to disagree with it because functions like MAX won't be limited to just the preceding and current rows.You can disagree all you want, but the MAX is limited as soon as you add an ORDER BY clause and that limit defaults to the answer marked as correct in the question.
Drew
Edited to remove answer to question.
Ok... so explain how that might work if the current row isn't the max row. I still think that BOL has explained it incorrectly.
I do think that BOL is poorly worded, but the information is there.
Quoting from OVER Clause
If ORDER BY is not specified entire partition is used for a window frame. This applies only to functions that do not require ORDER BY clause. If ROWS/RANGE is not specified but ORDER BY is specified, <answer obscured> is used as default for window frame. This applies only to functions that have can accept optional ROWS/RANGE specification. For example, ranking functions cannot accept ROWS/RANGE, therefore this window frame is not applied even though ORDER BY is present and ROWS/RANGE is not.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply