SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQLServerCentral apologizes and you can win a book


SQLServerCentral apologizes and you can win a book

Author
Message
lien.revanth
lien.revanth
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 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'
Neil Burton
Neil Burton
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3398 Visits: 10158
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
kgresham
kgresham
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 187
;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
waqasshami
waqasshami
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 46
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


Mauricio_
Mauricio_
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1194 Visits: 291
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 Smile
Cheers,

Mauricio
cabbagetreecustard
cabbagetreecustard
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 127
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;
SQLTuna
SQLTuna
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1440 Visits: 328
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
T. C.
T. C.
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1056 Visits: 461
I have the book, its well worth reading.
Zoltán Horváth
Zoltán Horváth
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 60
Well. It didn't worth it, for sure.

And now, my application for an Itzik Ben-Gan book! Smile


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


SQL_By_Chance
SQL_By_Chance
Old Hand
Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)

Group: General Forum Members
Points: 309 Visits: 299
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.”
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search