December 6, 2013 at 12:55 pm
I used ROW_NUMBER() OVER to speed up a query that returned the latest status for an "Activity" entity that is linked to a user-defined set of interface tabs. The code used to depend on a GROUP BY and HAVING that was quite slow in comparison.
WITH StatusHistory (ActivityID, TabID, StatusReportID, StatusOrder) AS (
SELECT
ASR.ActivityID
, StatusVar.TabID
, ASR.StatusReportID
, ROW_NUMBER () OVER (PARTITION BY ASR.ActivityID, ActStat.ActivityStatusTypeID ORDER BY ASR.ActivityID, ActStat.ActivityStatusTypeID, ASR.StatusReportDate DESC) [StatusOrder]
FROM dbo.ActivityStatusReport [ASR]
INNER JOIN dbo.ActivityStatus [ActStat] ON ActStat.ActivityStatusID = ASR.ActivityStatusID
INNER JOIN dbo.ActivityStatusType [StatusVar] ON StatusVar.ActivityStatusTypeID = ActStat.ActivityStatusTypeID
And StatusVar.ActivityStatusTypeEnabled = 1
)
SELECT
UsedTabs.ActivityID
, AllTabs.TabID [TabID]
, SH.StatusReportID
FROM dbo.AppTab [AllTabs]
INNER JOIN dbo.AppActivityTab [UsedTabs] ON UsedTabs.TabID = AllTabs.TabID
INNER JOIN StatusHistory [SH] ON SH.ActivityID = UsedTabs.ActivityID
And SH.TabID = UsedTabs.TabID
And SH.StatusOrder = 1
WHERE AllTabs.CMSTabEnabled = 1
And UsedTabs.[Enabled] = 1
I have a couple of Itzik's books, but not this one.
December 6, 2013 at 1:11 pm
SELECT AccountActivityID, FK_EntityID, ActivityTypeID, ActivityDate, DebitAmount,
FK_CourtID, PaidTo, AccountActivity.NoticeID, AttRefNum, SR.Plaintiff, T.DefList,
(ROW_NUMBER() OVER(Order by AccountActivity.NoticeID) + (@StartCheck-1)) AS CheckNumber
FROM AccountActivity
inner join ServiceRequest SR on SR.NoticeID = AccountActivity.NoticeID
left join @TableOut T on T.NoticeID = SR.NoticeID
WHERE (ActivityTypeID = 2)
AND ( (@ActivityID IS NULL AND PrintDate IS NULL )
OR (AccountActivityID = @ActivityID AND PrintDate IS NOT NULL))
ORDER BY NoticeID
December 6, 2013 at 2:08 pm
Yello! Pick me! 🙂
select UserID, sum(ServerInstanceCount) OVER (Partition by UserID) as UserServerCount from User u join ServerInstance si on u.Id = si.UserId;
December 6, 2013 at 2:50 pm
Unfortunately, any forum that alllows community posts is going to run into plagarism. Nobody reviewing the incoming posts is going to be familiar with every other published article on the Internet. It's all in how you handle the situation when it is discovered. And what a great way. Who wouldn't want free stuff from Itzik?!
Putting a little Christmas spirit into the mix!
DECLARE @Products TABLE
(
ProductId INTEGER,
ProductName NVARCHAR(25)
)
DECLARE @Orders TABLE
(
OrderId INTEGER,
DeliveryDate DATE,
ProductID INTEGER,
Quantity INTEGER
)
INSERT INTO @Products (ProductId, ProductName)
VALUES (12, 'Drummers Drumming'),
(11, 'Pipers Piping'),
(10, 'Lords a Leaping'),
(9, 'Ladies Dancing'),
(8, 'Maids a Milking'),
(7, 'Swans a Swimming'),
(6, 'Geese a Laying'),
(5, 'Golden Rings'),
(4, 'Calling Birds'),
(3, 'French Hens'),
(2, 'Pipers Piping'),
(1, 'Partridge in a Pear Tree')
-- Fill the order table
;WITH CTEProducts (ProductID, ProductName, DayOfChristmas)
AS
(
SELECT
ProductId,
ProductName,
12 AS DayOfChristmas
FROM @Products
WHERE ProductId <= 12
UNION ALL
SELECT
P.ProductId,
P.ProductName,
DayOfChristmas - 1
FROM @Products P
JOIN CTEProducts CTE ON P.ProductId = CTE.ProductID
WHERE P.ProductId <= DayOfChristmas - 1 AND DayOfChristmas > 1
)
INSERT INTO @Orders (OrderId, DeliveryDate, ProductID, Quantity)
SELECT 1000+DayOfChristmas, DATEADD(DAY, DayOfChristmas-1, '2013-12-25'), ProductID, ProductID FROM CTEProducts
-- Determine Total Sales Quantity by Product
SELECT DISTINCT P.ProductName, ProductQuantityTotal FROM @Products P
JOIN
(
SELECT ProductID, DeliveryDate, Quantity AS OrderQuantity, SUM(Quantity) over (partition by ProductID) AS ProductQuantityTotal from @Orders
) D ON P.ProductId = D.ProductID
ORDER BY P.ProductName, ProductQuantityTotal
-- Determine Delivery Requirements
SELECT P.ProductName, DeliveryDate, OrderQuantity, ProductQuantityTotal FROM @Products P
JOIN
(
SELECT ProductID, DeliveryDate, Quantity AS OrderQuantity, SUM(Quantity) over (partition by ProductID) AS ProductQuantityTotal from @Orders
) D ON P.ProductId = D.ProductID
ORDER BY DeliveryDate, P.ProductID
December 6, 2013 at 3:23 pm
Thank you for apologizing so publically. It's good to see SQL Server Central cares about honesty and acts appropriately.
SELECT DISTINCT OrganizationLevel
,COUNT(NationalIDNumber) OVER (PARTITION BY OrganizationLevel) AS LevelCount
FROM [HumanResources].[Employee]
Russ
December 6, 2013 at 3:42 pm
Love Mr. Ben-Gan's books!! One of the authors I look for when purchasing a new SQL Server book.
select ROW_NUMBER() over (partition by TABLE_SCHEMA, TABLE_NAME
order by TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE),* from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
December 6, 2013 at 6:37 pm
Nice one!
This is an example of the use of the "OVER" clause (cited from MS Exam 70-461 Training Kit - Microsoft Press, Authors: Itzik Ben-Gan, Dejan Sarka, Ron Talmage - which I can highly recommend. I am using it to prepare for 70-457 at present):
SELECT custid, orderid, val,
SUM (val) OVER(PARTITION BY custid) AS custtotal,
SUM (val) OVER() AS grandtotal
FROM Sales.OrderValues;
December 7, 2013 at 9:06 am
It is pretty disconcerting that somebody is trying to make money by taking some body elses work without attribution. It is one thing to organize coursewhere by surveying multiple topical sources and including those in your own work, but you must reference the original source.
Below is a simple CTE to create a line_number for a detail table insert.
with titles (id, report_name, r_l1, r_l2, r_l3, title, line_no)
as
(
select *, ROW_NUMBER() over (partition by report_id order by report_id, level_1, level_3, level_4) from
(select distinct s.REPORT_ID, s.report_name, level_1, LEVEL_3, LEVEL_4, TITLE_1
from ESGACTTR t inner join ESGGARSH s
on t.LEVEL_3 = SUBSTRING(s.report_name,2,4)
and t.LEVEL_4 = SUBSTRING(s.report_name,7,2)
and TITLE_ID = -1
and ACCT_TYPE = 'E'
and ACCT_YEAR = 2014
and s.REPORT_GROUP = 'expbud') as v
)
insert into ESGGARSD
(REPORT_ID, LINE_NO, LEVEL_1, LEVEL_2, LEVEL_3, LINE_DESCR, REPORT_YEAR)
select id, line_no, r_l1, r_l2, r_l3, upper(title), 2014
from titles
December 7, 2013 at 10:49 am
I am very tempted to just quote someone else's query with OVER 😀
December 7, 2013 at 10:59 am
Yes, but this one is mine. I just wrote it yesterday. I did see a few that looked like they were straight out of BOL 😉
December 7, 2013 at 11:54 am
SELECT U.UserId, U.FirstName, EU.EventId, E.EventName, 'Yes' as RSVP, EU.Attended,
COUNT(EU.UserId) OVER (PARTITION BY U.UserId ) AS UserTotalRSVPs,
SUM(EU.Attended) OVER (PARTITION BY U.UserId ) AS UserTotalAttended
FROM EventUser EU
INNER JOIN Users U ON U.UserID = EU.UserID
INNER JOIN Events E ON EU.EventID = E.EventId
December 7, 2013 at 12:14 pm
Too bad this happens but glad it was corrected. Itzik Ben-Gan is one of the folks I follow in the SQL Server world.
December 7, 2013 at 4:12 pm
I teach online at Kaplan University. There are serious disciplinary actions taken if students plagiarize.
Here is some code I wrote to illustrate GROUP BY. First, the table is defined and rows inserted:
-- create table vehicles
create table dbo.vehicles
( Year int null
,Makevarchar(15)null
,Modelvarchar(15)null
,BodyStylevarchar(30)null
,Priceintnull
);
go
insert vehicles values
(2000, 'Ford', 'Mustang', '2-Door Coupe', 8000)
,(1999, 'Honda', 'Civic', '2-Door Coupe', 5000)
,(2000, 'Chevrolet', 'Cavalier', '2-Door Coupe', 5000)
,(1999, 'Ford', 'Taurus', '4-Door Sedan', 7000)
,(1999, 'Toyota', 'Camry', '4-Door Sedan', 9000)
,(2000, 'Toyota', 'Camry', '4-Door Sedan', 10000)
,(2002, 'Toyota', 'Prius', 'Hybrid', 16000)
,(2001, 'Toyota', 'Prius', 'Hybrid', 14000)
,(2003, 'Toyota', 'Prius', 'Hybrid', 18000)
,(2003, 'Honda', 'Civic', 'Hybrid', 17000)
,(2003, 'Ford', 'Mustang', 'Convertible', 17000)
,(1999, 'Ford', 'Mustang', 'Convertible', 9000)
,(1998, 'Ford', 'Windstar', 'Cargo Van', 5000)
,(2000, 'Ford', 'Windstar', 'Cargo Van', 9000)
,(2000, 'Chevrolet', 'Silverado', 'Pickup Truck', 15000)
,(2001, 'Chevrolet', 'Silverado', 'Pickup Truck', 17000)
;
Next, here are two queries against the vehicles table that illustrate the use of OVER:
-- Query 1
SELECT *
, AVG(Price) OVER(PARTITION BY BodyStyle) as AvgPriceByBodyStyle
FROM dbo.vehicles;
-- Query 2
SELECT *
, AVG(Price) OVER(PARTITION BY BodyStyle) as AvgPriceByBodyStyle
, Price - AVG(Price) OVER(PARTITION BY BodyStyle) as CompareToAvg
FROM dbo.vehicles;
December 7, 2013 at 7:38 pm
-- Create sample data
-- Each Poster gets their name (I'm pretending their name is unique),
-- a posting ID, a flag to confirm their post has an OVER clause,
-- and a random number.
[font="Courier New"]WITH Sample AS (
SELECT 'James' AS Poster, 50 AS PostingID, 1 AS HasOVER
, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 AS SemiRandom UNION ALL
SELECT 'John' , 51, 0, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL
SELECT 'Robert' , 52, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL
SELECT 'James' , 53, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL
SELECT 'Mary' , 54, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL
SELECT 'Linda' , 55, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL
SELECT 'Michael', 56, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL
SELECT 'Maria' , 57, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL
SELECT 'Susan' , 58, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL
SELECT 'Paul' , 59, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL
SELECT 'Mark' , 60, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL
SELECT 'Lisa' , 61, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL
SELECT 'Mary' , 62, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL
SELECT 'Brian' , 63, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL
SELECT 'Ian' , 64, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL
SELECT 'Chris' , 65, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL
SELECT 'Jose' , 66, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL
SELECT 'Anton' , 67, 1, CAST(RAND(CHECKSUM(NEWID())[/font]) * 100 AS INT) + 1
)[/font]
-- In the spirt of fairness, multiple postings by the same "poster" don't
-- increase the odds of winning this great prize - it actually
-- decreases the odds 🙂 (MAX function)
-- Only postings that have an OVER example are considered (WHERE clause)
, Winners AS (
[font="Courier New"]SELECT Poster
,MAX(SemiRandom) OVER (PARTITION BY Poster) AS SemiRandomWorst
FROM Sample
WHERE HasOVER = 1
)[/font]
-- The next step is to pick the top ten, but I'll leave that for someone else
-- to use for their OVER post 🙂
December 8, 2013 at 9:39 am
Malcolm Wynden (12/7/2013)
-- Create sample data-- Each Poster gets their name (I'm pretending their name is unique),
-- a posting ID, a flag to confirm their post has an OVER clause,
-- and a random number.
[font="Courier New"]WITH Sample AS (
SELECT 'James' AS Poster, 50 AS PostingID, 1 AS HasOVER
, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 AS SemiRandom UNION ALL
SELECT 'John' , 51, 0, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL
SELECT 'Robert' , 52, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL
SELECT 'James' , 53, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL
SELECT 'Mary' , 54, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL
SELECT 'Linda' , 55, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL
SELECT 'Michael', 56, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL
SELECT 'Maria' , 57, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL
SELECT 'Susan' , 58, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL
SELECT 'Paul' , 59, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL
SELECT 'Mark' , 60, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL
SELECT 'Lisa' , 61, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL
SELECT 'Mary' , 62, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL
SELECT 'Brian' , 63, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL
SELECT 'Ian' , 64, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL
SELECT 'Chris' , 65, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL
SELECT 'Jose' , 66, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL
SELECT 'Anton' , 67, 1, CAST(RAND(CHECKSUM(NEWID())[/font]) * 100 AS INT) + 1
)[/font]
-- In the spirt of fairness, multiple postings by the same "poster" don't
-- increase the odds of winning this great prize - it actually
-- decreases the odds 🙂 (MAX function)
-- Only postings that have an OVER example are considered (WHERE clause)
, Winners AS (
[font="Courier New"]SELECT Poster
,MAX(SemiRandom) OVER (PARTITION BY Poster) AS SemiRandomWorst
FROM Sample
WHERE HasOVER = 1
)[/font]
-- The next step is to pick the top ten, but I'll leave that for someone else
-- to use for their OVER post 🙂
You can keep it very simple using the one of the very rules for the contest. As you say, have the server extract a list of people's handles (their names aren't required for registration) and their email addresses from the participants of this thread according too the rules of participation. Of course, this list would be filtered by DISTINCT. Then, the selection is easy by using OVER with no chance of a tied random number by using OVER and NEWID(). Here's an example using Adventure works, which is also my submittal for this contest.
SELECT RowNumber = ROW_NUMBER() OVER (ORDER BY NEWID())
,Handle = ContactID --Simulating the "handle" here
,EmailAddress
INTO dbo.WinnerOrder
FROM AdventureWorks.Person.Contact
;
The reason for not limiting it to the TOP 10 is because some good folks may turn the book down because they already have it or because they cannot be contacted due to a defunct email address or they simply don't respond.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 241 through 255 (of 287 total)
You must be logged in to reply to this topic. Login to reply