Help on SQL query

Question

Help on SQL query

Viewing 14 posts - 31 through 43 (of 43 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

ravigour2000 (7/10/2012)
Thanks Rookie it worked. 🙂

Yes, it worked... on 3 rows. See the post immediately above for why you MUST NOT use this method. If you don't want to use the Quirky Update method, then use a Cursor and While Loop. It will be MUCH faster than subareddy's method even for small numbers of rows (say, 1000).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GSquared SSC Guru Points: 260824 More actions · Answer 2

Jeff Moden (7/10/2012)
SQLKnowItAll (7/10/2012)
ravigour2000 (7/10/2012)
Thanks Rookie it worked. 🙂
Hmm... So, because it worked on a set of 3 records you will accept it? Do you even know what that is doing? Do you know what a cartesian product is? Do you understand triangular joins? Did you even bother to read the article I linked to?
Perhaps that's a matter of semantics. A VIEW would require a "single query" whether it's an actual view or an "Inline view" such as a CTE or Derived Table. The OP's original question specifically stated...
i wanted[font="Arial Black"] a single UPDATE query[/font] which result cumulative data in last column

I think you may have quoted the wrong post.

Assuming you meant to quote the post where I asked where Views came into the question, we can argue semantics all day, but I'll just state up front that a query for an update isn't the same as a View in my book. If they are the same thing to you, Sam's view definition, using a recursive CTE, would still answer your question about how it can be done in a not-a-view single-query update, or the defined View can be used as the source for an Update From or Merge statement.

However that may be, I still think it's an irrelevant tangent, since I'd use a CLR function for the running total, and then use that for either an Update From or Merge.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

ChrisM@Work (7/10/2012)
Jeff Moden (7/9/2012)
In 2008 or less, I'd REALLY like to know how to create a view that does a running total without forming a Triangular Join. I don't believe it's possible but sure am willing to learn a new trick.
USE tempdb
GO
DROP TABLE Simpletable
SELECT id, aNumber = ABS(CHECKSUM(NEWID()))%50
INTO Simpletable
FROM (SELECT TOP 100 id = ROW_NUMBER() OVER(ORDER BY name) FROM sys.columns) d
GO
CREATE VIEW [dbo].[vwRunningTotals]
AS
WITH Calculator AS (
SELECT id, aNumber, RunningTotal = aNumber
FROM Simpletable
WHERE id = 1
UNION ALL
SELECT tr.id, tr.aNumber, RunningTotal = lr.RunningTotal + tr.aNumber
FROM Calculator lr
INNER JOIN Simpletable tr ON tr.id = lr.id + 1
) SELECT id, aNumber, RunningTotal
FROM Calculator
--OPTION (MAXRECURSION 0) -- plan guide
GO
SELECT * FROM [dbo].[vwRunningTotals] WHERE id = 32
😉

Technically speaking, you're correct. That's a view that does running totals. But, will it withstand a deleted row or a skipped ID? I'm pretty sure that it won't.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

GSquared (7/10/2012)
Jeff Moden (7/10/2012)
SQLKnowItAll (7/10/2012)
ravigour2000 (7/10/2012)
Thanks Rookie it worked. 🙂
Hmm... So, because it worked on a set of 3 records you will accept it? Do you even know what that is doing? Do you know what a cartesian product is? Do you understand triangular joins? Did you even bother to read the article I linked to?
Perhaps that's a matter of semantics. A VIEW would require a "single query" whether it's an actual view or an "Inline view" such as a CTE or Derived Table. The OP's original question specifically stated...
i wanted[font="Arial Black"] a single UPDATE query[/font] which result cumulative data in last column
I think you may have quoted the wrong post.
Assuming you meant to quote the post where I asked where Views came into the question, we can argue semantics all day, but I'll just state up front that a query for an update isn't the same as a View in my book. If they are the same thing to you, Sam's view definition, using a recursive CTE, would still answer your question about how it can be done in a not-a-view single-query update, or the defined View can be used as the source for an Update From or Merge statement.
However that may be, I still think it's an irrelevant tangent, since I'd use a CLR function for the running total, and then use that for either an Update From or Merge.

I believe this misunderstanding is a matter of timing, Gus. At the time in this thread when I asked the question, you'd not yet mentioned the post.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GSquared SSC Guru Points: 260824 More actions · Answer 5

Jeff Moden (7/10/2012)
ChrisM@Work (7/10/2012)
Jeff Moden (7/9/2012)
In 2008 or less, I'd REALLY like to know how to create a view that does a running total without forming a Triangular Join. I don't believe it's possible but sure am willing to learn a new trick.
USE tempdb
GO
DROP TABLE Simpletable
SELECT id, aNumber = ABS(CHECKSUM(NEWID()))%50
INTO Simpletable
FROM (SELECT TOP 100 id = ROW_NUMBER() OVER(ORDER BY name) FROM sys.columns) d
GO
CREATE VIEW [dbo].[vwRunningTotals]
AS
WITH Calculator AS (
SELECT id, aNumber, RunningTotal = aNumber
FROM Simpletable
WHERE id = 1
UNION ALL
SELECT tr.id, tr.aNumber, RunningTotal = lr.RunningTotal + tr.aNumber
FROM Calculator lr
INNER JOIN Simpletable tr ON tr.id = lr.id + 1
) SELECT id, aNumber, RunningTotal
FROM Calculator
--OPTION (MAXRECURSION 0) -- plan guide
GO
SELECT * FROM [dbo].[vwRunningTotals] WHERE id = 32
😉
Technically speaking, you're correct. That's a view that does running totals. But, will it withstand a deleted row or a skipped ID? I'm pretty sure that it won't.

Nitpick. Modify the CTE to use a Row_Number() function call instead of an ID column, and it will still be functional with missing ID values, or even a table without an ID column at all. Can still be done in a View or in a derived table.

Performance will hurt, but the recursive CTE already has performance issues that have already been noted, so that's irrelevant to the point of Chris posting it.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

@Gus,

Ok... I've been looking at this thread for a bit for "Sam's view definition" and I'm not finding any "SAM" that posted. Did you mean "Chris' view definition"?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

GSquared (7/10/2012)
Nitpick. Modify the CTE to use a Row_Number() function call instead of an ID column, and it will still be functional with missing ID values, or even a table without an ID column at all. Can still be done in a View or in a derived table.
Performance will hurt, but the recursive CTE already has performance issues that have already been noted, so that's irrelevant to the point of Chris posting it.

BWAA-HAAA!!!! When I ask a question about code, I DO like it to be durable. 🙂 I also like it to be fast.

To be honest, though, I asked the question incorrectly because I already knew it could be done using an rCTE with ROW_NUMBER(). I should have asked if anyone could create a view to do a running total that would be at least as efficient as a While Loop. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Jeff Moden (7/10/2012)
ChrisM@Work (7/10/2012)
Jeff Moden (7/9/2012)
In 2008 or less, I'd REALLY like to know how to create a view that does a running total without forming a Triangular Join. I don't believe it's possible but sure am willing to learn a new trick.
USE tempdb
GO
DROP TABLE Simpletable
SELECT id, aNumber = ABS(CHECKSUM(NEWID()))%50
INTO Simpletable
FROM (SELECT TOP 100 id = ROW_NUMBER() OVER(ORDER BY name) FROM sys.columns) d
GO
CREATE VIEW [dbo].[vwRunningTotals]
AS
WITH Calculator AS (
SELECT id, aNumber, RunningTotal = aNumber
FROM Simpletable
WHERE id = 1
UNION ALL
SELECT tr.id, tr.aNumber, RunningTotal = lr.RunningTotal + tr.aNumber
FROM Calculator lr
INNER JOIN Simpletable tr ON tr.id = lr.id + 1
) SELECT id, aNumber, RunningTotal
FROM Calculator
--OPTION (MAXRECURSION 0) -- plan guide
GO
SELECT * FROM [dbo].[vwRunningTotals] WHERE id = 32
😉
Technically speaking, you're correct. That's a view that does running totals. But, will it withstand a deleted row or a skipped ID? I'm pretty sure that it won't.

You're absolutely correct, it won't. You could get around this limitation using ROW_NUMBER() but that loses the index (you can't create an index on a view which references a CTE).

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Jeff Moden (7/10/2012)
@Gus,
Ok... I've been looking at this thread for a bit for "Sam's view definition" and I'm not finding any "SAM" that posted. Did you mean "Chris' view definition"?

You can call me Al.

But not Sam.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ravigour2000 Valued Member Points: 52 More actions · Answer 10

Thanks Jared for your inputs, i did go through the article provided by, for Rookie query i updated it little and use it and just provided 3 record set as a example. For me its still early days in SQL i am still learning and your all inputs were of great help. Thanks

Mine query which i used:

--- cumulative update query

Update cumulative_one

SET Balance= (select SUM(c2.Deposit) from cumulative_one c2 where cumulative_one.Cust_no >= c2.Cust_no)

-- This query updates balance column for all rows in a table

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 11

ravigour2000 (7/11/2012)
Thanks Jared for your inputs, i did go through the article provided by, for Rookie query i updated it little and use it and just provided 3 record set as a example. For me its still early days in SQL i am still learning and your all inputs were of great help. Thanks
Mine query which i used:
--- cumulative update query
Update cumulative_one
SET Balance= (select SUM(c2.Deposit) from cumulative_one c2 where cumulative_one.Cust_no >= c2.Cust_no)
-- This query updates balance column for all rows in a table

Like I said, with relatively few rows, that query can kill a server. Please see the article link I previously posted for why.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GSquared SSC Guru Points: 260824 More actions · Answer 12

Jeff Moden (7/10/2012)
GSquared (7/10/2012)
Nitpick. Modify the CTE to use a Row_Number() function call instead of an ID column, and it will still be functional with missing ID values, or even a table without an ID column at all. Can still be done in a View or in a derived table.
Performance will hurt, but the recursive CTE already has performance issues that have already been noted, so that's irrelevant to the point of Chris posting it.
BWAA-HAAA!!!! When I ask a question about code, I DO like it to be durable. 🙂 I also like it to be fast.
To be honest, though, I asked the question incorrectly because I already knew it could be done using an rCTE with ROW_NUMBER(). I should have asked if anyone could create a view to do a running total that would be at least as efficient as a While Loop. 😛

Now, if that had been your question, I'd have to agree with you 100%. Maybe more.

Quirky update and CLR are the fastest, and most efficient (in terms of resource-use), when it comes to running totals and such.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

GSquared SSC Guru Points: 260824 More actions · Answer 13

Jeff Moden (7/10/2012)
@Gus,
Ok... I've been looking at this thread for a bit for "Sam's view definition" and I'm not finding any "SAM" that posted. Did you mean "Chris' view definition"?

Yes. Chris. That's what I get for posting while I'm seriously sleep-deprived. (Have you ever noticed how severe pain can make it difficult to get a good night's sleep? I sure have!)

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 14

GSquared (7/12/2012)
Jeff Moden (7/10/2012)
@Gus,
Ok... I've been looking at this thread for a bit for "Sam's view definition" and I'm not finding any "SAM" that posted. Did you mean "Chris' view definition"?
Yes. Chris. That's what I get for posting while I'm seriously sleep-deprived. (Have you ever noticed how severe pain can make it difficult to get a good night's sleep? I sure have!)

?? Yes. Brachial plexus neuritis four years ago. Wassup Gus?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden