Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

SQLServerCentral apologizes and you can win a book Expand / Collapse
Author
Message
Posted Thursday, December 5, 2013 1:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 5, 2013 1:51 AM
Points: 1, Visits: 21
I like way when people publicly apologize when something went wrong.

Microsoft has given below simple definition which helps in understanding its intention to use

"the OVER clause defines a window or user-specified set of rows within a query result set. A window function then computes a value for each row in the window. You can use the OVER clause with functions to compute aggregated values such as moving averages, cumulative aggregates, running totals, or a top N per group results."


Sample query to get the rank of each row within the partitioned by parameter_id of a result set(to fetch password parameters from sys.all_parameters).

SELECT RANK() OVER(Partition by parameter_id order by max_length desc) as rank_, * from sys.all_parameters where name like '%@password'
Post #1519894
Posted Thursday, December 5, 2013 1:20 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:28 AM
Points: 1,107, Visits: 3,544
I'd been pointed at Itzik's work to solve a problem I'd set myself so a copy of the book would be good.

Here's the code I used to deal with the first half of the problem.
create table #Orders (OrderID int)
insert into #Orders values
(111101),
(111102),
(111103),
(111105),
(111106),
(111110),
(111111),
(111112),
(111113),
(111119),
(111125)
;

with cte as(
select
row1 = ROW_NUMBER() over(order by sl.OrderID desc)
,sl.OrderID
from #Orders sl
group by sl.OrderID
)

select
Row = ROW_NUMBER() over(order by o1.row1)
,diff = o2.OrderID - o1.OrderID
,o1.OrderID
into #missing
from cte o1
join cte o2 on o1.row1 = (o2.row1+1)
where
(o2.OrderID - o1.OrderID) > 1

select * from #missing

drop table #missing,#Orders

Edit Spelling



On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help
Post #1519896
Posted Thursday, December 5, 2013 1:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 3, 2014 11:19 AM
Points: 2, Visits: 142
;with src(val) as (
select 1 union all
select 2 union all
select 2 union all
select 3 union all
select 3 union all
select 3
),
row(val,rn) as (
select val,
row_number() over (partition by val order by (select null))rn
from src
)
select val from row where rn = 1

--use windows fn all the time for de-duping with business logic
Post #1519900
Posted Thursday, December 5, 2013 1:36 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 7:32 AM
Points: 1, Visits: 42
SET NOCOUNT ON
BEGIN TRY
BEGIN TRANSACTION

/********************************************************************************************/
PRINT '--> Update ClassificationCriteria';
UPDATE CC1 set CC1.Sequence = CC2.rownum FROM ClassificationCriteria AS CC1
JOIN
(SELECT ROW_NUMBER() OVER(PARTITION BY ClassificationID ORDER BY ClassificationID, ParentID, Sequence) - 1 AS rownum, CriteriaID FROM ClassificationCriteria)
AS CC2 ON CC1.CriteriaID = cc2.CriteriaID

/********************************************************************************************/

PRINT '==> Commiting transaction';
COMMIT TRANSACTION;

END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
PRINT '!!! Error: ' + ERROR_MESSAGE();
PRINT '<== Transaction has been rolled back';
END CATCH

Post #1519903
Posted Thursday, December 5, 2013 1:40 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 17, 2014 10:11 PM
Points: 399, Visits: 156
This is a definition for a field in a very nice view I've designed to solve a problem:
    (SELECT     INTERNETID
FROM (SELECT InternetId, row_number() OVER (ORDER BY InternetId) AS Fila
FROM PaydayLoans Loan
WHERE Loan.CUSTOMERID = cust.CUSTOMERID
GROUP BY InternetId) AS Temporal
WHERE Temporal.Fila = 3) = loan.InternetId THEN 1 ELSE 0 END) AS ThirdPayments

I'm sure Itzik Ben-Gan can find a better way to do this but I hope his book helps me :)
Cheers,

Mauricio



Post #1519904
Posted Thursday, December 5, 2013 1:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 19, 2014 7:41 PM
Points: 2, Visits: 117
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;

Post #1519907
Posted Thursday, December 5, 2013 2:06 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 2:03 AM
Points: 1,388, Visits: 275
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
@SQLTuna
sqltuna.blogspot.co.uk
Post #1519913
Posted Thursday, December 5, 2013 2:13 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, September 22, 2014 11:33 AM
Points: 738, Visits: 377
I have the book, its well worth reading.
Post #1519914
Posted Thursday, December 5, 2013 2:21 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 6:38 AM
Points: 1, Visits: 60
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

Post #1519915
Posted Thursday, December 5, 2013 2:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 25, 2014 10:05 AM
Points: 122, Visits: 292
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.”
Post #1519919
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse