December 10, 2013 at 11:00 am
bhw0006 48811 (12/9/2013)
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
Welcome to SSC and well done. You should take a look at this article if you can, it shows a faster, better way to generate a sequence of numbers. Below is a quick test I put together with 1,000,000 rows; the first method uses the Recursive Common Table Expression that you posted and the second one uses a tally table (look up "table of numbers" if you win the Ben Gan book 😉 ).
SET NOCOUNT ON;
SET STATISTICS TIME ON;
GO
-- Recursive CTE
WITH Numbers AS
(SELECT 1 AS Nr
UNION ALL
SELECT Nr + 1
FROM Numbers
WHERE Nr < 1000000
)
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
OPTION (MAXRECURSION 0);
-- Tally table
WITH
e1(Nr) AS--10
(SELECT Nr
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))t(Nr)
),
e2(Nr) AS--100
(SELECT (0)
FROM e1 a CROSS JOIN e1 b),
e3(Nr) AS--1000
(SELECT (0)
FROM e2 a CROSS JOIN e1 b),
Numbers(Nr) AS--1,000,000
(SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM e3 a CROSS JOIN e3 b
)
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
SET STATISTICS TIME OFF
GO
Results:
--recursive CTE--
SQL Server Execution Times:
CPU time = 10358 ms, elapsed time = 15802 ms.
--tally table--
SQL Server Execution Times:
CPU time = 2496 ms, elapsed time = 6668 ms.
The "Numbers" or "Tally" Table: What it is and how it replaces a loop.[/url]
-- Itzik Ben-Gan 2001
December 10, 2013 at 12:26 pm
Thanks SSC! Here is a query that runs against the 2012 Adventureworks database:
SELECT SalesOrderID
, SalesPersonID
, OrderDate
, SubTotal
, ROW_NUMBER() OVER (PARTITION BY OrderDate ORDER BY SubTotal DESC) AS OrderRankByDay
, DENSE_RANK() OVER (PARTITION BY OrderDate ORDER BY SubTotal DESC) AS DenseOrderRankByDay
, CUME_DIST() OVER (PARTITION BY OrderDate ORDER BY SubTotal DESC) AS CumDistByDay
, LAG(SubTotal, 1, NULL) OVER (PARTITION BY OrderDate ORDER BY SubTotal DESC) AS PreviousSubtotal
FROM Sales.SalesOrderHeader
December 10, 2013 at 12:38 pm
Plagirism is not a good thing.
below is the code to get the number of days left to christmas.
with DaysTillXMas as
(
select cast(convert(date, getdate()) as datetime) as RemDates
union all
select (RemDates + 1) from DaysTillXMas where (RemDates + 1) < '2013-12-25'
)
select row_number() over(order by (select 1)) as DatesCount, RemDates from DaysTillXMas OPTION (MAXRECURSION 0)
December 10, 2013 at 2:56 pm
I admit that I did have to consult the internet for some help on understanding what OVER does. I should play around with using this more.
I am a database administrator for a ticketing system and put together this query for number of days that a ticket has been opened, and comparing it to the group that was assigned to it:
SELECT Ticket#,
CONVERT(FLOAT(closedate-opendate) / (24*60*60)) AS ElapsedTimeOpen,
SUM(CONVERT(FLOAT(closedate-opendate) / (24*60*60))) OVER (partition by Group) AS SumElapsedTimeOpen,
AVG(CONVERT(FLOAT(closedate-opendate) / (24*60*60))) OVER (partition by Group) AS AverageElapsedTimeOpen,
Group
FROM tickets
WHERE closedate is not null
AND DATEADD(ss,opendate-14400, '01/01/1970') > getdate()-7 -- opened no later than a week ago
AND (closedate-opendate) > 86400 -- was opened for at least 1 day
ORDER BY opendate DESC
December 10, 2013 at 3:18 pm
It is nice to see that SQLServerCentral is supporting the author after the mishap.
I use the following query to get the first item in a list. I assume it has already been posted.
SELECT a.first_name, a.book_id
FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY a.user_id ORDER BY b.creation_date DESC) line, a.first_name, b.book_id
FROM a
INNER JOIN [books] b ON a.user_id = b.user_id
) a
WHERE a.line = 1
December 10, 2013 at 3:49 pm
Using ROW_NUMBER() for display purposes.
CREATE TABLE #Relationships
(
IDINT
,NameVARCHAR(50)
,ParentIDINT
)
INSERT INTO #Relationships(ID, Name, ParentID)
SELECT 1, 'Walt', NULL
UNION ALL SELECT 2, 'Frank', NULL
UNION ALL SELECT 3, 'Steven', 1
UNION ALL SELECT 4, 'Selma', 1
UNION ALL SELECT 5, 'Heidi', NULL
UNION ALL SELECT 6, 'Stephanie', NULL
UNION ALL SELECT 7, 'Joyce', 5
UNION ALL SELECT 8, 'George', 5
UNION ALL SELECT 9, 'Rebecca', 5
UNION ALL SELECT 10, 'Nathan', 3
UNION ALL SELECT 11, 'Mike', 3
UNION ALL SELECT 12, 'Charles', 3
;WITH Hierarchy AS
(
SELECT R2.NameAS Parent
, R1.NameAS Child
, ROW_NUMBER() OVER (PARTITION BY R2.ID ORDER BY R1.ID) AS RowNum
FROM #Relationships R1
JOIN #Relationships R2 ON R1.ParentID = R2.ID
)
-- only display parent's name once
SELECT CASE H.RowNum WHEN 1 THEN H.Parent ELSE '' END AS Parent
, H.Child
FROM Hierarchy H
DROP TABLE #Relationships
December 10, 2013 at 10:33 pm
Plagiarism of any kind is bad, but i am sure none of us here want to get involved in that kind of practice. It must have got published on this forum by mistake and Mr. Ben-Gan will forgive us.
SQl is a very expressive and obvious language for all database developers. Below Code demonstrate use of Grouping to get aggregate on few columns and then get Rownumber for Output rows .
[Code]
SELECT
(ROW_NUMBER() OVER (ORDER BY DeliveryLocation, PONumber, PODate, Supplier, DeliveryDate, StockItem)) AS IRowNumber,
(CASE WHEN GROUPING(DeliveryLocation) = 0 THEN DeliveryLocation ELSE 'Buyer Total' END) AS [Buyer],
(CASE WHEN GROUPING(PONumber) = 0 THEN PONumber ELSE 'Document Total' END) AS [Number],
(CASE WHEN GROUPING(PODate) = 0 THEN CONVERT(VARCHAR(10), PODate, 103) ELSE 'Document Date Total' END) AS [PO Date],
(CASE WHEN GROUPING(Supplier) = 0 THEN Supplier ELSE 'Supplier Total' END) AS [Supplier],
(CASE WHEN GROUPING(DeliveryDate) = 0 THEN CONVERT(VARCHAR(10), DeliveryDate, 103) ELSE 'Delivery Date' END) AS [Delivery Date],
(CASE WHEN GROUPING(StockItem) = 0 THEN StockItem ELSE 'Stock Item' END) AS [Stock Item],
SUM(OrderedQty) AS [Ordered Qty], SUM(OrderedValue) AS [Ordered Value],
SUM(ReceivedQty) AS [Received Qty], SUM(ReceivedValue) AS [Received Value],
SUM(PendingQty) AS [Pending Qty], SUM(PendingValue) AS [Pending Value]
FROM PURCASEORDERDETAIL
GROUP BY DeliveryLocation, PONumber, PODate, Supplier, DeliveryDate, StockItem
WITH ROLLUP
[/Code]
December 11, 2013 at 5:21 am
The book looks great. Buy it if you don't win!
/*--------------------------------------
Import Excel worksheet with row numbers
2013-12-11 Jeff Brooks
Output columns are RowNum, F1, F2, F3,...
*/--------------------------------------
IF OBJECT_ID('tempdb..#ExcelImport') IS NOT NULL DROP TABLE #ExcelImport
SELECT
RowNum = ROW_NUMBER() OVER(ORDER BY GETDATE())
, *
INTO #ExcelImport
FROM
OPENROWSET(
'Microsoft.ACE.OLEDB.12.0'
, 'Excel 12.0 XML;HDR=NO;Database=C:\Code\TestFiles\ExecTest.xlsx;'
, 'SELECT * FROM [TestSheet1$]'
);
SELECT *
FROM #ExcelImport
December 11, 2013 at 5:32 am
jk_in_San_Diego (12/5/2013)
Once upon a time, in the dark ages of printed textbooks and such, plagarism was not only frowned upon, it was cause for immediate dismissal or expulsion from university.To claim the work of another as your own is at best fraud and at worst theft.
Always cite your sources but use them as sources of information, not the sources of your content!
Hear, hear!
December 11, 2013 at 7:47 am
Great book!
WITH TEMP
AS
(
SELECT
row_number() over (order by book_id) as ID
,BOOK_DETAIL_ID
FROMBOOKS
)
SELECT *
FROM TEMP
December 11, 2013 at 11:44 am
First time playing around with this.
Looks like something I would use from time to time.
USE AdventureWorksDW2008R2
go
with temp_tbl
as (
select
ROW_NUMBER() over (order by PostalCode desc) as Row_Number
,GeographyKey
,City
,StateProvinceCode
,StateProvinceName
,CountryRegionCode
,EnglishCountryRegionName
,PostalCode
,SalesTerritoryKey
from
dbo.DimGeography
where
CountryRegionCode = 'US'
and StateProvinceCode = 'MN')
SELECT *
FROM temp_tbl
where Row_Number = 3
December 12, 2013 at 11:40 am
Last evening I went to a Pacific NW SQL user group meeting. The guest speaker was Itzik Ben-Gan.
Before that:
I opened the “Apologies…” email last week from SQLServerCentral.com (very catchy title, by the way, as I only open 30-60% of these depending on personal bandwidth). I thought, “Hmmm, this name sounds familiar.” Sure enough, he was the speaker that I had just read about in the PNWSQL email, and the previous month’s presenter had spoken very highly of him. I had already planned on going, but maybe I could chat with him after the presentation and get an inside track on winning a new book. Possibly I could get an interesting quote to share with Steve Jones and all of the contributors and followers of the website.
So then went into research mode. Who was Itzik? What was he about? What has he done? How could I appeal to him? I saw one of his other books on Amazon, Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012. When I saw that one of the co-authors was Ron Talmage the president of PNWSQL, I bought it. What the heck, I’d been feeling it is time to update my certs from SQL 2000. I also felt that it would be better if I showed up with a book “in hand”. I could ask for an autograph after the presentation which could create the opportunity to ask Itzik about the “unintended plagiarism” on SQLServerCentral.com, the reaction by Steve Jones and the “win a book written by Itzik” offer.
So I arrived at the presentation with high expectation. I was not disappointed. The title of the presentation was, “Creative Uses of the APPLY Operator.” The room was fairly packed. There were more people there than the prior three months combined.
Itzik is an engaging and smooth presenter. He can manage the presentation devices, write code, present theory, and have it all make sense faster than I could take notes. He has the charisma of a movie star, a cool accent and the enthusiasm of a child at Christmas. The presentation reminded me of watching Roberto Benigni accepting his Oscar for Best Actor in his 1997 movie, Life Is Beautiful (look it up on YouTube.) I learned more in the first 10 minutes than I could process, and I had 65 minutes to go.
By the way, the much easier way to go would have been to study his previous work. He gave out 8 or so books during the course of the evening. If I was better prepared, I could have answered a question correctly and wouldn’t have to write this essay.
I may have been star struck.
By the end of the presentation, I walked up to him. I actually opened my Amazon box in front of him. My comment was, “I brought my own book, would you sign it for me.” He was very accessible and charming. He wrote, “Happy Querying!” and signed my book.
During this time, I asked him if he had heard about the kerfuffle on SQLServerCentral.com. He said he had, and had received an email from Steve Jones.
Yet, I think I caught him off guard. I think he wanted questions about the “Apply Operator”. I said, “I was hoping for some words that I could use that would help me “cinch” the “win a book” contest. He responded enthusiastically, “use the code that I presented that included the sum(sum(val)), that is really good. That will earn you a free book.”
Unfortunately, by that point in the presentation, I was already a page behind and had given up on copying and instead chose to enjoy the show.
I tried one more probing question, “I was hoping for a quote.” Again, he gave me a curious look and politely said, “I’m drawing a blank.”
I felt as though I had failed in my mission. I was hoping to have something interesting to quote, and I was even hoping to create a memorable moment. Yet, I started feeling as if I were being impolite and/or boorish. I was disappointed in my attempt and somewhat frustrated with myself.
I turned to Ron Talmage to have him sign my book as well. I was juggling my Amazon box, SQL book, notepad and pen. Somehow the SQL book goes forcefully flying ten feet across the table and past Itzik. He reached over to pick up the book. As he hands it to me, he exuberantly quips, “Here’s a quote for you. Say that you tried to kill me with my own book.”
So, I wish I had more of Itzik’s latest code to share with you (he offered), but I feel the time crunch of posting this to the forum.
Here is some code that I use on my personal database project. Basically, I have a horseracing database that I use for research. It took me a while to figure out how to compare horses within each race. This code allows me to create a view from one of my tables. I can then query the view to look for the top ranked horse within a race. I also use it to compare previous similar races to an upcoming race and determine how “deep” I may need to go to find today’s winner. For example, in historical races like today’s first race 10 of the 12 winners were in the top 2 (or 6) ranked Prime Power.
Steve, Thank you for the opportunity and space.
Create View [dbo].[vL2RPrime] AS
Select
[Track]
,[Date]
,[Year]
,[Month]
,[Day]
,[Race#]
,[ProgramNumber]
,[PostPosition]
,[HorseName]
,[TodaysTrainer]
,[PrimePower]
,RANK() OVER
(PARTITION BY (DTR) ORDER BY PrimePower DESC) AS RankPrime
FROM vLSIV2ndWin
December 12, 2013 at 12:26 pm
kennethrbell:
Really nice post about your attendance at Itzik's presentation. Although we're all a bunch of techies, it's nice to read a story that touches upon ourselves as people, even when muddling through a techie moment. I was fortunate enough to attend one of Itzik's presentation a few years ago in Orlando. He has such an ease when presenting. I was amazed at how simple and elegant his code would be when illustrating a concept -- often by just querying the Pubs database.
How cool for you.
Thanks for the story,
--pete
December 12, 2013 at 12:55 pm
kennethrbell (12/12/2013)
Steve, Thank you for the opportunity and space.
You are welcome, and thanks for the note.
December 12, 2013 at 12:55 pm
My apologies, but the contest is over. I picked the winners this morning.
I'll post something with names tomorrow or Monday.
Viewing 15 posts - 271 through 285 (of 287 total)
You must be logged in to reply to this topic. Login to reply