December 5, 2013 at 9:14 pm
WITH CTE
AS
(
SELECT t.x
FROM
( VALUES
(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
)t (x)
),
CTE2
AS
(
SELECT ROW_NUMBER() OVER(ORDER BY c.x) AS RowNum
FROM CTE c
CROSS JOIN CTE c2
CROSS JOIN CTE c3
CROSS JOIN CTE c4
CROSS JOIN CTE c5
),
CTE3
AS
(
SELECT CAST(c.RowNum AS NUMERIC(15,2)) AS ProductCost,
c.RowNum%5 AS ProductID
FROM CTE2 c
)
SELECT DISTINCT c.ProductID,
SUM(c.ProductCost) OVER(PARTITION BY c.ProductID ORDER BY c.ProductID) AS TotalProductCost
FROM CTE3 c
ORDER BY c.ProductID
;
December 5, 2013 at 10:01 pm
How does this look...simple
Use MYDb;
GO
SELECT ROW_NUMBER() OVER(ORDER BY X) AS Row,
A, B, C
FROM Z;
December 5, 2013 at 10:27 pm
I applaud your steps to respect Ben-Gan's intellectual property. He is certainly the go-to resource for depth of knowledge on all sorts of querying topics.
Here is a bit of actual code I wrote as part of a solution to list families and include the date of birth for the oldest child, which was important for the specific reporting requirements of the complete solution. What is shown below was used as a CTE in the complete query solution.
select
families.family_id,
children.person_id,
children.nick_name,
children.last_name,
children.birth_date,
children.gender,
oldest_child_birth_date = min(children.birth_date) over (partition by families.family_id)
from
dbo.core_person as children inner join
dbo.core_family_member as families on children.person_id = families.person_id inner join
dbo.core_lookup as family_roles on families.role_luid = family_roles.lookup_id
where
family_roles.lookup_value = 'Child'
December 6, 2013 at 12:56 am
Okay, here's my ticket.
Once upon a time there was a song Bridge over troubled water.. by Simon & Garfunkel!
select row_number() over (order by bridge_id) as troubled_water_id, river
from rivers
Best of all to SQL community
Grega Jesih
PS
I read this article especially because as soon as I spotted a legend name of Mr BenGan, it caught my attention.
December 6, 2013 at 1:54 am
Good form.
Hopefully this will put a smile on Jeff's face (I'm using your TallyTable!) I love that by the way, so handy.
I used something similar to this recently...it's strange and was for a very specific purpose but it does have window functions in it. 🙂
DECLARE @Start datetime = DATEADD(minute,5,GETDATE())
,@End datetime = DATEADD(minute,12,GETDATE());
;WITH SomeData AS (
SELECT TOP 25 100 AS [RowId]
,'Something: '+CHAR(N+47) AS [Something]
,DATEADD(minute,N-1,GETDATE()) AS [SomeDate]
FROM dbo.TallyTable
UNION ALL
SELECT TOP 6 200 AS [RowId]
,'Something: '+CHAR(N+47) AS [Something]
,DATEADD(minute,N,GETDATE()) AS [SomeDate]
FROM dbo.TallyTable
UNION ALL
SELECT TOP 35 300 AS [RowId]
,'Something: '+CHAR(N+47) AS [Something]
,DATEADD(minute,N-2,GETDATE()) AS [SomeDate]
FROM dbo.TallyTable
)
SELECT RowId
,Something
,SomeDate
,@Start AS [StartDate]
,@End AS [EndDate]
,CASE WHEN SomeDate > @Start AND SomeDate < @End THEN 'Between @Start And @End'
WHEN SomeDate <= @Start THEN 'Less Than @Start'
ELSE 'Greater Than @End'
END AS [TimeState]
,ROW_NUMBER() OVER(PARTITION BY RowId ORDER BY SomeDate DESC) AS [RowNum]
,ROW_NUMBER() OVER(PARTITION BY RowId
,CASE WHEN SomeDate > @Start AND SomeDate < @End THEN 2
WHEN SomeDate <= @Start THEN 3
ELSE 1
END
ORDER BY SomeDate DESC) AS [LessThan_@Start_Records]
,ROW_NUMBER() OVER(PARTITION BY RowId
,CASE WHEN SomeDate > @Start AND SomeDate < @End THEN 2
WHEN SomeDate <= @Start THEN 3
ELSE 1
END
ORDER BY SomeDate ASC) AS [GreaterThan_@End_Records]
FROM SomeData;
December 6, 2013 at 1:58 am
select row_number() over (order by type)
from sys.objects
December 6, 2013 at 2:51 am
use AdventureWorks
SELECT SalesOrderID, ProductID, OrderQty,
ROW_NUMBER() OVER ( PARTITION BY SalesOrderID ORDER BY ProductID ) AS GNum,
ROW_NUMBER() OVER ( ORDER BY ProductID ) AS CNum
FROM Sales.SalesOrderDetail
December 6, 2013 at 3:02 am
Thats a nice gesture from you to issue a public apology and give away Bem-Gan's books.
Here is a production SQL frm my DB to get latest payment details by account num.
select * from (
select rank() over (partition by ACCOUNT_NUM order by PAYMENT_DATE desc) as Rnk, ACCOUNT_NUM, PAYMENT_DATE, PAYMENT_AMOUNT
from FACT_ACCOUNTT_PAYMENTT
where PAYMENT_DATE between '2013-10-01' and '2013-11-30'
) as x where Rnk =1
order by ACCOUNT_NUM, PAYMENT_DATE asc
December 6, 2013 at 3:03 am
It happens sometimes and its indeed great for apology.
Simple example..
SELECT SCHEMA_NAME(schema_id) ,
name ,
ROW_NUMBER() OVER ( PARTITION BY SCHEMA_NAME(schema_id) ORDER BY SCHEMA_NAME(schema_id), name ) AS TableIndex
FROM sys.tables
December 6, 2013 at 3:43 am
I use window functions all the time! Would love to know how to enhance them even further.
SELECT t.Name,
t.CountryRegionCode,
t.[Group],
t.SalesYTD,
RANK() OVER (PARTITION BY [Group] ORDER BY SalesYTD) AS SubRank,
RANK() OVER (ORDER BY SalesYTD) AS OverallRank
FROM sales.SalesTerritory t
😀
December 6, 2013 at 5:36 am
Fragments in full-text catalog
select object_name([table_id]) as TableName
, count([fragment_id]) over(partition by table_id) as Fragments
from sys.fulltext_index_fragments
December 6, 2013 at 6:15 am
My opinion: MSSQL window function are a great way to manipulate data, promoting the set based approach
--An example of how to simply remove duplicates from the data table using window function
--we want to delete duplicates and keep the records that were last added (in accordance with the desired key)
create table #names(first_namenvarchar(20), last_name nvarchar(50), record_added datetime)
insert into #names (first_name, last_name, record_added )
select
'John', 'Doe', getdate() union all
select
'John', 'Doe', dateadd(hh,-1,getdate()) union all
select
'John', 'Doe', dateadd(hh,-2,getdate()) union all
select
'Jane', 'Doe', getdate() union all
select
'Jane', 'Doe', dateadd(hh,-1,getdate()) union all
select
'Jane', 'Doe', dateadd(hh,-2,getdate()) union all
select
'Some', 'Other Guy', null
select * from #names
begin try
create unique index main on #names (first_name, last_name) --no can do for there are duplicate first_name+last_name values
end try
begin catch
;with duplicates as
(select
row_number() over (partition by first_name, last_name order by first_name, last_name, record_added desc) rn
from
#names)
delete from
duplicates
where
rn > 1
create unique index main on #names (first_name, last_name) --Works just fine, for there are no duplicates left
end catch
select * from #names
drop table #names
December 6, 2013 at 6:34 am
Nice! 😀
Query to show lowest date for a key where date as more than five entries
;WITH Test (SomeKey,SomeDate,RowNo)
AS (
SELECTSomeKey,SomeDate
,ROW_NUMBER() OVER (PARTITION BY SomeKey ORDER BY SomeDate ASC)
FROMSomeTable
GROUPBY SomeKey,SomeDate
HAVINGCOUNT(*) > 5
)
SELECTSomeKey,SomeDate
FROMTest
WHERERowNo = 1
Far away is close at hand in the images of elsewhere.
Anon.
December 6, 2013 at 6:58 am
It's a shame this guy's work was plagiarized.
I have been enjoying the window functions!
;WITH
cteT1 AS
(
SELECT 1 i UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
rnT2 AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 i FROM cteT1 a, cteT1 b
),
rnT3 AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 i FROM cteT1 a, cteT1 b, cteT1 c
)
SELECT
i
,ROW_NUMBER() OVER (PARTITION BY i/5 ORDER BY i) iByFive
FROM rnT3
ORDER BY i
December 6, 2013 at 7:45 am
CREATE TABLE #nn0(
iden [int] NOT NULL,
seqnum [int] NOT NULL,
thisYR [int] NOT NULL,
result [int] NULL,
CONSTRAINT PK_0 PRIMARY KEY CLUSTERED (iden ASC,seqnum ASC))
INSERT INTO #nn0 (iden, seqnum, thisYR, result)
VALUES
(1,1,2012,2), (1,2,2012,4), (1,3,2013,3), (1,4,2013,2), (1,5,2013,4),
(2,1,2014,1), (3,1,2014,3), (4,1,2014,5)
SELECT * FROM #nn0
SELECT iden, thisYR, result
FROM (SELECT iden, seqnum, thisYR, result
, ROW_NUMBER() OVER (PARTITION BY iden ORDER BY iden, thisYR DESC) AS rk
FROM #nn0) x
WHERE rk = 1
DROP TABLE #nn0
--Easy
Viewing 15 posts - 211 through 225 (of 287 total)
You must be logged in to reply to this topic. Login to reply