December 5, 2013 at 1:51 am
I would like to win the book, I find this really useful to delete duplicate items -
create table #T (identifier int, name varchar(100))
insert into #T select 1, 'Fred'
insert into #T select 1, 'Fred'
insert into #T select 2, 'Dave';
Select * from #T;
with duplicates
as
(Select row_number() over (partition by identifier order by identifier) rn
From #T)
Delete from duplicates where rn > 1
Select * from #T;
December 5, 2013 at 2:06 am
I'm glad you named and shamed the culprit!
Below is a very quick tally table:
declare @NoOfRows int = 1000000
-- cross join allows up to 2047*2047 rows = 4,190,209.
;with tally as (
select top (@NoOfRows) row_number() over(order by a.type) as n
from master.dbo.spt_values a
cross join master.dbo.spt_values b
where a.type = 'P'
and b.type = 'P'
)
select *
from tally
Mike Lewis
December 5, 2013 at 2:13 am
I have the book, its well worth reading.
December 5, 2013 at 2:21 am
Well. It didn't worth it, for sure.
And now, my application for an Itzik Ben-Gan book! 🙂
CREATE TABLE #CopyrightedContent (id int IDENTITY(1,1), Author varchar(255), ArticleURL varchar(255), PublicationDate date)
INSERT INTO #CopyrightedContent(Author, ArticleURL, PublicationDate) VALUES
('Itzik Ben-Gan', 'http://tsql.solidq.com/books/windowfunctions2012/', '2012-04-10'),
('Itzik Ben-Gan', 'http://tsql.solidq.com/books/tsqlfund2008/', '2008-10-22'),
('Itzik Ben-Gan', 'http://tsql.solidq.com/books/source_code/SQL%20Server%20MVP%20Deep%20Dives%20-%20Chapter%2005.txt', '2009-11-01')
INSERT INTO #CopyrightedContent(Author, ArticleURL, PublicationDate)
-- HERE COMES THE LOGIC:
SELECT 'Me' AS Author, LastWork, CURRENT_TIMESTAMP FROM (
SELECT ArticleURL AS LastWork, PublicationDate, ROW_NUMBER() OVER(PARTITION BY Author ORDER BY PublicationDate DESC) AS RecentRank FROM #CopyrightedContent
) A WHERE RecentRank = 1
SELECT * FROM #CopyrightedContent
DROP TABLE #CopyrightedContent
December 5, 2013 at 2:34 am
Very good initiative.
http://www.sqlservercentral.com/articles/SQLServerCentral/105392/
-- Delete Duplicate Data
; WITH CTE AS (
Select A,B, RowNum = Row_Number() OVER (ORDER BY A)
From t_TAble1
)
DELETE FROM CTE
WHERE RowNum > 1
Thanks,
Ankit
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”
December 5, 2013 at 2:36 am
Is it OK if I copy and paste one of the code sections from higher up?
(Whoa!!! OK, not really going to!)
SELECT
ID_key
,Cake_key
,holiday_period
,ROW_NUMBER() OVER (ORDER BY ID_key, Party_No) -
ROW_NUMBER() OVER (PARTITION BY ID_key, Cake_key, holiday_period ORDER BY ID_key, Party_No) AS all_ranked
FROM Source_Cake
December 5, 2013 at 2:54 am
well not posting for book but to value SSC to show courage to accept and apologize publicly
small query to get row number in different way
select row_no = row_number() over (order by id)
from tbl
Ashish
------------------------------------------------------------------------------------
Ashish
December 5, 2013 at 3:00 am
Great attitude from SQLSC.
Plagiarism is bad for many reasons, then, the guilty involved must be uncovered without piety.
Here is my humble and sample code using OVER(clause):
/* Brings the third of rank (dense) using salary as criteria */
WITH CTE
AS(
SELECT
EmployeeID,
EmployeeName,
Department,
Salary,
DENSE_RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) as RankOrder
FROM @Employees
)
SELECT
c.EmployeeID,
c.EmployeeName,
c.Department,
c.Salary
FROM CTE c
WHERE c.RankOrder = 3
December 5, 2013 at 3:01 am
Following the theme of the article, I don't claim the idea of this to be mine, but I've found OVER functions are very handy for calculating median times.
DECLARE @test-2 TABLE
(
TaskIDINT IDENTITY(1,1),
TaskVARCHAR(15),
SecondsINT
);
INSERT INTO @test-2 (Task,Seconds)
SELECT'Random Task' + CAST(ABS(CHECKSUM(NEWID()))%5+1 AS CHAR(1)),
(ABS(CHECKSUM(NEWID()))%20)+1
FROMdbo.GetNums(1,100000);
--Not the most random spread of data you'll see!
SELECT * FROM @test-2;
SELECTSQ1.Task,
SQ1.RowCnt AS UseCount,
SQ1.AVGDuration,
AVG(SQ1.Seconds + 0.0) AS MedianSeconds
FROM
(
SELECTT.Task,
T.Seconds,
ROW_NUMBER() OVER (PARTITION BY T.Task ORDER BY T.Seconds ASC) AS RowNum,
COUNT(*) OVER (PARTITION BY T.Task) AS RowCnt,
AVG(Seconds + 0.0) OVER (PARTITION BY T.Task) AS AVGDuration
FROM@test-2 AS T
) AS SQ1
WHERERowNum IN ((RowCnt + 1) / 2, (RowCnt + 2) / 2)
GROUP
BYSQ1.Task,
SQ1.RowCnt,
SQ1.AVGDuration;
December 5, 2013 at 3:03 am
Unfortunately, some people like to bask in the glories of others. I had one interview where a candidate bought in a portfolio of his work and while reviewing it, I came across a script which looked suspiciously like a copy. I challenged it and showed him the exact same code, including comments - The only change had been to replace the originators name with his own. Unfortunately for the interview candidate, he left a reference to the originators blog...
Needless to say, he was a little unsure what to say or do next and we agreed it was best to call the interview to a close!
SELECT Forename + ' ' + Surname AS Driver, LapTime, ROW_NUMBER() OVER (ORDER BY LapTime) AS Position
FROM dbo.Qualifying
December 5, 2013 at 3:31 am
Hi,
This trick uses ROW_NUMBER with a CTE to remove duplicates based on a field:
This removes duplicate references of the field CUSTOMER_ID from the table DATATABLE using the highest (most recent) value of DATE_CREATED to determine priority.
create table DATATABLE
(CUSTOMER_ID int,
DATE_CREATED DATETIME
)
insert into DATATABLE ( CUSTOMER_ID, DATE_CREATED)VALUES (1,GETDATE())
insert into DATATABLE ( CUSTOMER_ID, DATE_CREATED)VALUES (1,GETDATE())
insert into DATATABLE ( CUSTOMER_ID, DATE_CREATED)VALUES (2,GETDATE())
insert into DATATABLE ( CUSTOMER_ID, DATE_CREATED)VALUES (2,GETDATE())
insert into DATATABLE ( CUSTOMER_ID, DATE_CREATED)VALUES (3,GETDATE())
select * from DATATABLE
;with _dupes as
(
select _dr = ROW_NUMBER() OVER (PARTITION BY CUSTOMER_ID ORDER BY DATE_CREATED DESC) from DATATABLE
)
delete _dupes where _dr > 1
select * from DATATABLE
December 5, 2013 at 3:38 am
Just saw Itzik at a local user group. Don't copy his work!
I'm also using window functions for deleting duplicates (among a ton of other things):
DELETE t
FROM ( SELECT ROW_NUMBER() OVER ( PARTITION BY p1, p2, p3, p4 ORDER BY p5 DESC ) AS rownum
FROM #test
WHERE Status = 1
) AS t
WHERE rownum > 1
December 5, 2013 at 3:43 am
What I find useful is turning a aggregate function into a windowed function, which saves having to use the "group by" clause
Here is the count(*) used in conjuction with another column and not a group by in sight
select table_Name, Column_Name, cast(ordinal_position as varchar(3)) + ' of ' + cast(count(*) over (partition by table_name) as varchar(3)) as NumberOfColumns
from information_schema.columns
order by TABLE_NAME, ORDINAL_POSITION
December 5, 2013 at 3:57 am
Firstly - well done on dealing with the plagiarism issue so openly - and a free book giveaway never hurts!
We use windows functions extensively in our code base, particularly for custom aggregates based on ranking. Here's a typical (but heavily anonymised) example - which picks the latest available delivery point for a customer from a series of orders:
--First CTE would be a base table in production code
WITH CTE_SourceData (PersonID, OrderID, OrderDate, DeliveryPoint)
AS
(
SELECT 1, 10, '2013-01-01', 23 UNION ALL
SELECT 1, 11, '2013-01-02', 22 UNION ALL
SELECT 1, 12, '2013-01-03', NULL UNION ALL
SELECT 2, 13, '2013-02-01', 25 UNION ALL
SELECT 2, 14, '2013-02-02', NULL
)
,CTE_Ranking AS
(
SELECTPersonID
,DeliveryPoint
,ROW_NUMBER() OVER (PARTITION BY PersonID
ORDER BY CASE WHEN DeliveryPoint IS NOT NULL THEN 0 ELSE 1 END ASC,
OrderDate DESC) AS DeliveryPointRank
FROMCTE_SourceData
)
SELECTPersonID, DeliveryPoint FROM CTE_Ranking
WHEREDeliveryPointRank = 1
December 5, 2013 at 4:18 am
I have his book on the 70-461 exam and it's very easy to read - hopefully this new one is the same!
Here's a query I wrote:
-- now the tricky bit - have any of the tables changed?
-- if we union the 2 queries together, any duplicates will only have 1 row, anything that's
-- changed will have 2, so we just mark those as needing syncing....simples!
UPDATE DRL
SET[SyncRequired] = 1,
[WebItem] = 1
FROM[DRL_Magento_Feed] DRL WITH (NOLOCK)
JOIN(SELECT[ItemID],
ROW_NUMBER() OVER(PARTITION BY [ItemID] ORDER BY [ItemID]) AS [Row]
FROM
(SELECTI.[ID] AS [ItemID],
I.[ItemLookupCode],
ISNULL(Y.[Alias],'') AS [Ean_Code],
I.[Description] AS [StandardDescription],
I.[SubDescription1] AS [ProductLinkingCode],
(I.[Quantity] - I.[QuantityCommitted]) + CASE ISNULL(DRIA.[Presell],'No')
WHEN 'Yes' THEN ISNULL(X.[QtyOnPO],0)
ELSE 0 END AS [QtyAvailable],
I.[WebItem],
I.[Inactive] AS [Inactive],
'STOCK' AS [AttributeSetName],
ISNULL(C.[Name],'') AS [Category],
ISNULL(C.[Code],'') AS [CategoryCode],
CAST(I.[ExtendedDescription] AS NVARCHAR(MAX)) AS [Name],
I.[SubDescription1] AS [QuickFindCode],
CAST(ISNULL(I.[Notes],'') AS NVARCHAR(MAX)) AS [Description],
--ISNULL(DRIT.[Text01],'') AS [ShortDescription],
CASE UPPER(ISNULL(DRIA.[Sizes_All],'One size'))
WHEN '<NONE>' THEN 'One size'
ELSE ISNULL(NULLIF(DRIA.[Sizes_All],''),'One size') END AS [Size],
I.[Price],
I.[MSRP],
I.[Weight],
ISNULL(NULLIF(DRIA.[Brand],'<None>'),'') AS [Brand],
ISNULL(NULLIF(DRIA.[Colour_Exact],'<None>'),'') AS [Colour],
ISNULL(NULLIF(DRIA.[Colour_General],'<None>'),'') AS [Colour_Group],
ISNULL(T.[Percentage],0) AS [TaxPercentage]
FROM [Item] I
JOIN [DRL_Magento_Feed] DRL
ON DRL.[ItemID] = I.[ID]
LEFT JOIN[Department] D
ON D.[ID] = I.[DepartmentID]
LEFT JOIN[Category] C
ON C.[ID] = I.[CategoryID]
LEFT JOIN [DRItem] DRI
ON DRI.[ItemID] = I.[ID]
LEFT JOIN [DRItemAttribute] DRIA
ON DRIA.[ItemID] = I.[ID]
LEFT JOIN [DRItemText] DRIT
ON DRIT.[ItemID] = I.[ID]
LEFT JOIN [Supplier] S
ON S.[ID] = I.[SupplierID]
LEFT JOIN [ItemTax] IT
ON IT.[ID] = I.[TaxID]
LEFT JOIN [Tax] T
ON T.[ID] = IT.[TaxID01]
LEFT JOIN
(SELECTPOE.[ItemID], SUM([QuantityOrdered] - [QuantityReceivedToDate]) AS [QtyOnPO]
FROM[PurchaseOrderEntry] POE
JOIN[PurchaseOrder] PO
ON POE.[PurchaseOrderID] = PO.[ID]
AND POE.[StoreID] = PO.[StoreID]
WHEREPO.[Status] < 2-- closed
ANDPO.[POType] < 2 -- don't include transfers
ANDPOE.[QuantityOrdered] > POE.[QuantityReceivedToDate]
GROUP BY POE.[ItemID]) X
ON X.[ItemID] = I.[ID]
OUTER APPLY(SELECT TOP 1 [Alias] FROM [Alias]
WHERE [ItemID] = I.[ID]
ORDER BY [ID] DESC) Y
UNION
SELECT[ItemID],
[ItemLookupCode],
[Ean_Code],
[StandardDescription],
[ProductLinkingCode],
[QtyAvailable],
[WebItem],
[Inactive],
[AttributeSetName],
[Category],
[CategoryCode],
CAST([Name] AS NVARCHAR(MAX)) AS [Name],
[QuickFindCode],
CAST([Description] AS NVARCHAR(MAX)) AS [Description],
[Size],
[Price],
[MSRP],
[Weight],
[Brand],
[Colour],
[Colour_Group],
[TaxPercentage]
FROM[DRL_Magento_Feed] DRL WITH (NOLOCK)) X)Y
ON DRL.[ItemID] = Y.[ItemID]
JOIN [Item] I WITH (NOLOCK)
ON I.[ID] = DRL.[ItemID]
WHERE Y.[Row] > 1
AND I.[WebItem] = 1
Viewing 15 posts - 16 through 30 (of 287 total)
You must be logged in to reply to this topic. Login to reply