December 8, 2013 at 9:53 am
Getting back to the subject of this thread... the corrective actions taken on this incident by the proprietors of SQLServerCentral and it's wonderful Editor-in-Chief, Mr. Steve Jones, are part of the reason why I call SQLServerCentral.com my "home". I also take my hat off to the participants of this forum for the level of professionalism normally shown on an everyday basis. Well done one and all.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 8, 2013 at 9:32 pm
I like Itzik Ben-Gan's books. Writes really well.
with cte1
as
(
rn = row_number() over (partition by p.person_id, p.enc_id order by p.person_id)
, p.last_name
, p.first_name
, p.date_of_birth
, pe.enc_id
, pe.enc_date
from person p
inner join patient_encounter pe
on p.person_id = pe.person_id
)
select *
from cte1
order by last_name, first_name, rn
December 9, 2013 at 2:13 am
Finding meadian value of field Number in table SampleData
DECLARE@Median FLOAT;
SELECT@Median = ISNULL(AVG([Number]), 1)
FROM(
SELECT[Number],
ROW_NUMBER() OVER ( ORDER BY [Number] ASC ) AS [NumberRank],
(
SELECTCOUNT(*)
FROM[SampleData]
) AS [NumberCount]
FROMSampleData
GROUP BY[Number]
) AS [FBU]
WHERE[FBU].[NumberRank] = ( [FBU].[NumberCount] / 2 + 1 )
OR [FBU].[NumberRank] = ( ( [FBU].[NumberCount] + 1 ) / 2 );
December 9, 2013 at 2:40 am
Select The latest result for each root categories with their childs category:
WITH LatestResult AS
(SELECT result.*,
ROW_NUMBER() OVER(PARTITION BY result.RootParentID ORDER BY result.PublishedTime DESC) AS RowNo
FROM Tracker.GetResultWithRootParent() result
WHERE result.[Status] != 255)
SELECT [ResultID],
[ExpressionHierarchyID],
[Title],
[Content],
[Url],
[ImageUrl],
[Publisher],
[PublishedTime],
[Language],
[MetaTitle],
[MetaDescription],
[MetaKeywords],
[ArticleBody]
FROM LatestResult
WHERE RowNo <= 3
ORDER BY PublishedTime DESC
December 9, 2013 at 3:42 am
Thanks for being so bold to admit your mistake and apologising in Public. I admire the gesture.
I would love the book.
Query :
WITH CTE AS
(
SELECT 1 AS Num
UNION ALL
SELECT Num + 1
FROM CTE
WHERE Num < 1000
)
SELECT
Num,
ROW_NUMBER() OVER(PARTITION BY Num%10 ORDER BY Num)-1 AS Tens
FROM CTE
ORDER BY 1
OPTION (MAXRECURSION 0)
December 9, 2013 at 3:48 am
This book is great! I hope to win some.
--Example of some index statistics
select o.name, ColNo, InclColNo,
rank() over (order by ColNo desc) as RnkColNo,
rank() over (order by InclColNo desc) as RnkInclColNo,
dense_rank() over (order by ColNo desc) as DnsRnkColNo,
dense_rank() over (order by InclColNo desc) as DnsRnkInclColNo,
i.*
from sys.indexes i
join sys.objects o on i.object_id = o.object_id
cross apply (
select count(1) as ColNo from sys.index_columns ic
where ic.index_id = i.index_id and ic.object_id = i.object_id and ic.is_included_column = 0
) as ColNo
cross apply (
select count(1) as InclColNo from sys.index_columns ic
where ic.index_id = i.index_id and ic.object_id = i.object_id and ic.is_included_column = 1
) as InclColNo
where i.type <> 0
order by DnsRnkColNo,RnkColNo,DnsRnkInclColNo,RnkInclColNo,o.name,i.name
--Example of some row paging
SELECT [RowNum], {column commalist}
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY {Order By Columns commalist}) AS [RowNum], {column commalist}
FROM Some_View
WHERE 1 = 1
AND ( {boolean exp} )
) AS RET
WHERE RowNum >= @_PageFrom
AND RowNum <= @_PageTo
December 9, 2013 at 5:04 am
I was introduced to Window Functions by Itzik's contribution to the '70-461 Querying Microsoft SQL Server 2012 Training Kit' and my eyes were opened (I wish I'd heard of them sooner!)...
use AdventureWorks2012;
select distinct p.ProductID, p.Name,
min(pch.StandardCost) over(partition by pch.productid) as MinStandardCost,
max(pch.StandardCost) over(partition by pch.productid) as MaxStandardCost
from Production.Product p left join Production.ProductCostHistory pch
onp.ProductID = pch.ProductID;
December 9, 2013 at 7:15 am
Very nice in 2012 :). Takes windowed functions one step further.
USE [tempdb]
CREATE TABLE Sales (Id INT PRIMARY KEY IDENTITY(1,1), SaleAgentId INT NOT NULL, ProductId INT NOT NULL, SaleDateTime DATETIME2, Amount DECIMAL(9,2) NOT NULL)
GO
INSERT Sales (SaleAgentId, ProductId, SaleDatetime, Amount)
VALUES (ABS(CHECKSUM(NEWID())) % 10 + 1, ABS(CHECKSUM(NEWID())) % 5 + 1, DATEADD(DAY,ABS(CHECKSUM(NEWID())) % (1+DATEDIFF(DAY,'01-jan-2013','31-dec-2013')),'01-jan-2013'), 100)
GO 100
SELECTSaleAgentId, SaleDatetime, Amount, SUM(Amount) OVER (PARTITION BY SaleAgentId ORDER BY SaleDateTime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) SumedAmount
FROMSales
DROP TABLE Sales
December 9, 2013 at 7:25 am
Itzek is a SQL Ninja!!
December 9, 2013 at 10:59 am
SQL Agent jobs that failed on the last run:
SELECT J.name
, run_datetime = cast(STUFF(RIGHT(run_date, 4), 3,0, '/') + '/' + LEFT(run_date, 4) + ' ' + REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT('000000' + CAST(run_time as varchar(10)), 6), 3, 0, ':' ), 6, 0, ':' ), 8) as datetime) /* hh:mm:ss 24H */, 9), 14), ':000', ' ') as datetime)
, H.run_status
, H.step_id
, H.step_name
, H.message
FROM (
SELECT H.job_id
, H.run_date
, H.run_time
, H.run_status
, H.step_id
, H.step_name
, H.message
, RowNum = row_number() OVER (partition by job_id ORDER BY H.run_date DESC, H.run_time DESC, Case H.run_status WHEN 1 THEN 1000 ELSE H.run_status END, H.step_id desc)
FROM msdb.dbo.sysjobhistory H) H
INNER JOIN msdb.dbo.sysjobs J ON H.job_id = J.job_id
WHERE H.RowNum = 1
AND J.enabled = 1
AND H.run_status <> 1
December 9, 2013 at 11:32 am
This is awesome ya'll are doing this
if object_id('tempdb..#table') is not null drop table #table
go
select first_name, last_name, age
into #table
from (
select 'Chuck' as first_name, 'Norris' as last_name, '32' as age union all
select 'Chuck' as first_name, 'Norris' as last_name, '32' as age union all
select 'Jean Claude' as first_name, 'Van Damme' as last_name, '32' as age union all
select 'Bruce' as first_name, 'Willis' as last_name, '32' as age union all
select 'Bruce' as first_name, 'Willis' as last_name, '32' as age union all
select 'Jean Claude' as first_name, 'Van Damme' as last_name, '32' as age union all
select 'Jean Claude' as first_name, 'Van Damme' as last_name, '32' as age union all
select 'Chuck' as first_name, 'Norris' as last_name, '32' as age
) as d1
alter table #table add id int identity(1,1)
select
row_number() over(partition by first_name, last_name, age order by last_name) as r,
first_name, last_name, age
from
#table
delete q from (
select
row_number() over(partition by first_name, last_name, age order by last_name) as r,
first_name, last_name, age
from
#table
) as q
where
r > 1
select
row_number() over(partition by first_name, last_name, age order by last_name) as r,
first_name, last_name, age
from
#table
December 9, 2013 at 12:03 pm
Rolling average of current row and previous 4. Only good for SQL 2012
WITH Numbers AS
(SELECT 1 AS Nr
UNION ALL
SELECT Nr + 1
FROM Numbers
WHERE Nr < 100
)
SELECT
Nr,
CASE
WHEN Nr >= 5
THEN AVG(cast(Nr as Money)) OVER(ORDER BY Nr ROWS BETWEEN 4 PRECEDING and CURRENT ROW)
ELSE 0
END AS RollingAvg
FROM Numbers
ORDER BY 1
December 10, 2013 at 8:05 am
That was a very forthright and respectable response to a sensitive topic. Nicely done SQLServerCentral.
I would love to write a query that uses the OVER clause but ironically can't do it without copying someone else's code. That's why I could really use this book!
🙂
Eric
December 10, 2013 at 9:49 am
Awesome move on SQLServerCentral's part - outstanding!
The book on Windowing functions is a must-read!
December 10, 2013 at 10:34 am
Great example of community policing!!!
With that in mind, here's my entry for the Windowing book!!
This sample shows how, given tables with customer, products, and orders, you can extract two groupings (TotalItemsEverBought with OrderHistoryTotal, and the second group showing just a single order OrderTotal) with windowing functions.
selectdistinct
C.id as CustomerId,
C.first_name,
C.last_name,
P.Name as ProductName,
S.id as OrderID,
P.price,
count(2) OVER(PARTITION BY P.id, C.id) TotalItemsEverBought,
sum(P.price) OVER(PARTITION BY S.id, C.id) OrderTotal,
sum(P.price) Over(PARTITION BY C.id) OrderHistoryTotal
from Sales S
inner join Customer C on S.customer_id=C.id
inner join SalesItems I on S.id=I.order_id
inner join Products P on I.product_id=P.id
order by C.id, OrderId
Jason Carter
Tampa, Florida
"Anyone who stops learning is old, whether at twenty or eighty. Anyone who keeps learning stays young. The greatest thing in life is to keep your mind young" - Henry Ford
Viewing 15 posts - 256 through 270 (of 287 total)
You must be logged in to reply to this topic. Login to reply