December 5, 2013 at 4:32 am
Honorable and classy move SQL Server Central. Too much plagiarism on the internet as people try to bolster their image. Calling them out like this has to happen more often.
December 5, 2013 at 4:34 am
WINDOW FUNCTION ARE EASY TO USE TO PERFORM OPERATIONS ON SET OF ROWS
CREATE TABLE #SOURCE ( ID INT, NAME VARCHAR(50))
INSERT INTO #SOURCE
VALUES (1, 'ABC'), (3,'PQR' ), (4, 'XYZ')
SELECT ID,NAME,ROW_NUMBER() OVER (ORDER BY ID) AS ROW_NO FROM #SOURCE
December 5, 2013 at 4:38 am
I just wanted to Kudos to the SQL ServerCentral folks for the quick action and response to their unfortunate mishap. Class Act!
December 5, 2013 at 4:38 am
Well done on the quick resolution to the problem.
I've always felt that there's an openness to this site, that welcomes input from anyone, and often the discussions below the article contain more information and sometimes corrections without criticism of the original author.
As an inexperienced SQL user i could really use a copy of the book, and I don't feel that I can contribute a useful example of original code but here's one that I use:
SELECT
DENSE_RANK() OVER(PARTITION BY Date, ItemCode, Batch ORDER BY TimePrinted) AS IssueNumber
gets an issue number for recipes that have been issued multiple times. I usually use ORDER BY TimePrinted DESC because I'm only interested in the most recent copy, and that will always be IssueNumber = 1
Edited because I accidentally posted it before i was ready!
December 5, 2013 at 4:38 am
Most of the examples so far have demonstrated the OVER clause used with aggregate (MIN, AVG, COUNT, etc) functions, or with ranking (ROW_NUMBER, DENSE_RANK etc) functions. In SQL2012, the functionality was extended to analytical functions too, such as LAG and LEAD.
The best use for these is the ability to reference previous or next rows, without the need for a self-join, which was the main method prior to SQL2012.
So let's set up some test data
declare @Sales table
(
SalesYear int,
SalesQuarter int,
SalesAmount money
)
insert into @Sales select 2011,1,5000.00
insert into @Sales select 2011,2,2000.00
insert into @Sales select 2011,3,6000.00
insert into @Sales select 2011,4,7000.00
insert into @Sales select 2012,1,1000.00
insert into @Sales select 2012,2,2500.00
insert into @Sales select 2012,3,1800.00
insert into @Sales select 2012,4,5000.00
In it's simplest form, the LAG and LEAD functions give the previous and next values. The parameters in lag(SalesAmount,1,0) say that we want to go back 1 offset, and use 0 as the default value if there is no value at that offset. Otherwise you'd get NULL
As per other OVER() windowing functions, you can use the PARTITION clause to limit the window, so for example if we wanted the same as above but only get the previous values where they are in the same SalesYear, we can do this.
select
SalesYear,
SalesQuarter,
SalesAmount,
PrevSalesAmount = lag(SalesAmount,1,0)over(order by SalesYear, SalesQuarter),
NextSalesAmount = lead(SalesAmount,1,0)over(order by SalesYear, SalesQuarter)
from @Sales
SalesYear SalesQuarter SalesAmount PrevSalesAmount NextSalesAmount
----------- ------------ --------------------- --------------------- -----------------
2011 1 5000.00 0.00 2000.00
2011 2 2000.00 5000.00 6000.00
2011 3 6000.00 2000.00 7000.00
2011 4 7000.00 6000.00 1000.00
2012 1 1000.00 7000.00 2500.00
2012 2 2500.00 1000.00 1800.00
2012 3 1800.00 2500.00 5000.00
2012 4 5000.00 1800.00 0.00
(8 row(s) affected)
select
SalesYear,
SalesQuarter,
SalesAmount,
PrevSalesAmount = lag(SalesAmount,1,0)over(partition by SalesYear
order by SalesYear, SalesQuarter)
from @Sales
SalesYear SalesQuarter SalesAmount PrevSalesAmount
----------- ------------ --------------------- ---------------------
2011 1 5000.00 0.00
2011 2 2000.00 5000.00
2011 3 6000.00 2000.00
2011 4 7000.00 6000.00
2012 1 1000.00 0.00
2012 2 2500.00 1000.00
2012 3 1800.00 2500.00
2012 4 5000.00 1800.00
(8 row(s) affected)
December 5, 2013 at 4:39 am
I was sorry to hear about the plagiarism that occurred on the site, but SQL Central is handling it in a very gracious way. I took the opportunity to learn a little bit about the OVER clause in TSQL. Here is an example query:
SELECT p.FirstName, p.LastName
,ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS "Row Number"
,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson AS s
INNER JOIN Person.Person AS p
ON s.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.Address AS a
ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL
AND SalesYTD <> 0;
GO
December 5, 2013 at 4:42 am
I like Windows function, it just makes complex task simple. I use them all the time, I also wrote a column a while ago which explains all windows function available in SQL Server:
Regards,
Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)
http://basitaalishan.comDecember 5, 2013 at 5:09 am
One thing most people don't realize is that quoting an author's work, and attributing it properly, might not be plagiarism but it is still copyright infringement. Microsoft gives a certain amount of latitidue in allowing us to quote BOL to each other to help the community solve problems. Yet if they caught us reciting whole "chapters" of BOL in articles, I bet they'd come down on us like a load of bricks.
I believe people who take things like this whole sale from others' works just don't understand the subject and want to look smart without realizing the consequences to their own reputations. Or maybe they don't care that they will lose out on so many career opportunites. It's a shame, really.
I was going to give an example of OVER() using code I've actually put into production, but I can't find it at the moment. If I find it later, I'll add it to this post.
Found it! It's a double, non-recursive CTE.
With OrderDetail AS
(select dod.Key, dod.Num, dod.LastName, isnull(dod.FirstName,'') as FirstName,
ds.SellerID, dt.FullDate, dod.Code, dod.Type,
Row_Number() Over (Order by dod.Key) as MyOrder1
from dbo.DimOrderDetail dod
join dbo.DimSeller ds
on dod.SellerKey = ds.SellerKey
join dbo.DimTime dt
on dod.EffDt = dt.TimeKey
where dod.Num is NOT NULL),
Orders AS
(Select goi.No as Num, od.Key, goi.Type, goi.LastName, goi.FirstName,
goi.SellerID, goi.StDt, goi.Type2, goi.Code, goi.Term, goi.OBAmt, goi.IRAmt,
goi.Period, goi.IRB, goi.IIAmt, goi.Exp, goi.Cur, fo.CKey,
Row_Number() Over (Order by od.Key) as MyOrder2
from OrderDetail od
join REPORTSERVER.MyDB.dbo.vDBA_GetOrderInfo goi
on od.Num = goi.No
and od.LastName = goi.LastName
and od.FirstName = goi.FirstName
and od.CovCD = goi.Type
and od.InsCD = goi.Code
and od.SellerID = goi.SellerID
and od.FullDate = goi.StDt
join dbo.FactOrders fo
on od.Key = fo.Key)
Insert into dbo.Staging (Key, Type, Code, IRAmt, OBAmt, Term, Period, IRB,
IIAmt, Exp, Cur, StartDate, TimeKey, CKey)
(Select Key, Type, Code, IRAmt, OBAmt, Term, Period, IRB, IIAmt, Exp, Cur,
@TimeKey as StartDate, @TimeKey as TimeKey, CKey
from Orders);
EDIT: Scrubbed column names.
December 5, 2013 at 5:29 am
SELECT * FROM (
SELECT NTILE(4) OVER ( PARTITION BY gt.SubGroup ORDER BY gt.RowID ) nt, *
FROM dbo.SomeRandomTable gt)id
WHERE id.nt=2
This is an example where you have a table with RowID (identity column) and a secondary grouping column.
1,1
2,1
3,2
4,5
...
The NTILE function is set to split the result set into 4 groups. (Obviously this works best if each of your subgroups contains at least 4 rows of data)
Wrapping the whole statement in an additional select, lets you use the NTILE Function column and pick all the set data from each sub group that falls within the 25th to 50th percentile of the group.
thanks
Steve
December 5, 2013 at 5:36 am
Very simple example of how to use the OVER clause:
SELECT
Year(OrderDate),
ROW_NUMBER() over(Partition By Year(OrderDate)order by OrderDate) as RowNum,
*
FROM [Northwind].[dbo].[Orders]
ORDER BY Year(OrderDate), RowNum
😉
December 5, 2013 at 5:38 am
Glad to know that SSC is honest.
Im more of a .net developer, but i do have some sql skills. Here's my query that I used to find identify duplicate records by name and address.
select DENSE_RANK() over (order by upper(name), upper([address])) GroupID,
p.id ProviderID, npi, name, Name_First, Name_Last, [address], city, [state], zipcode
into #Provs
from wdls_providers p join WDLS_projectProviders pjp on p.id=pjp.providerID
join WDLS_groupProviders gp on p.id=gp.providerID
where Name is not null and ltrim(rtrim(name))<>'' and [address] is not null and ltrim(rtrim([address]))<>'' and
pjp.projectID=@projectID and gp.groupID=@groupID
December 5, 2013 at 5:42 am
While plagiarizing is a no-no, and was quickly acknowledged and the offending article removed, it certainly helped me to re-discover the Windows functions especially the use of the OVER clause. I applaud SQLServerCentral for their honesty and forthright correction.
December 5, 2013 at 5:56 am
SELECT e.ID,
COALESCE(e.ParentEstimateId, e.ID) AS EstimateNumber,
CAST(ROW_NUMBER() OVER (PARTITION BY COALESCE(e.ParentEstimateId, e.ID) ORDER BY COALESCE(e.ParentEstimateId, e.ID), e.ID) AS int) AS RowNumber
FROM dbo.Estimate e
INNER JOIN CTE_MostlyNonConversionParentEstimates cte ON COALESCE(e.ParentEstimateID, e.ID) = cte.ParentEstimateId
I don't use the windowing functions often, but there are those cases where it's a really great solution.
Rob
December 5, 2013 at 5:56 am
I'm just really impressed by the reaction that this site had to content that was lifted without attribution. A quick, scholarly reaction. I hope that Mr. Ben-Gan appreciated the response, and that this community knows that sharing knowledge is lofty, but giving credit is vital. Without it, it's theft, not sharing.
I wonder what Senator Paul would say about this!
December 5, 2013 at 5:58 am
Order #123 ships with N deliveries #ABC on Dec. 2, #DEF on Dec. 3, ..., #XYZ on Dec. 5.
The table Orders contains a record with
{
orderid: #123,
...
}
and the table Deliveries contains N records with
{
deliveryid: #ABC
deliverydate: 20131202,
orderid: #123,
...
},
{
deliveryid: #DEF
deliverydate: 20131203,
orderid: #123,
...
},
...,
{
deliveryid: #XYZ
deliverydate: 20131205,
orderid: #123,
...
}
Retrieving the date and id (and/or any other value) of the latest delivery regarding order #123 (and any other order with one or more deliveries) is as easy as a:
WITH OrderDeliveries (
orderid,
deliveryid,
deliverydate,
rn
) AS (
SELECT
orderid,
deliveryid,
deliverydate,
ROW_NUMBER() OVER (PARTITION BY orderid ORDER by deliverydate DESC, deliveryid DESC) AS rn
)
SELECT
orderid,
deliveryid,
deliverydate
FROM OrderDeliveries
WHERE rn = 1
Viewing 15 posts - 31 through 45 (of 287 total)
You must be logged in to reply to this topic. Login to reply