An interesting TSQL problem

B

Bohica69

Gawd
Joined
Jul 12, 2005
Messages
676
Firstly - I'm trying to figure out if this is possible to do without using a cursor. Using temporary tables and such is ok, but I'd rather avoid doing any cursors.

Here's the data (and a table)

DECLARE @TempTable TABLE(
ID INT
, Rate REAL
, Points REAL
)

INSERT INTO @TempTable(ID, Rate, Points) VALUES ( 1, 4.5, 0.5)
INSERT INTO @TempTable(ID, Rate, Points) VALUES ( 3, 4.625, 0.125)
INSERT INTO @TempTable(ID, Rate, Points) VALUES ( 5, 4.75, -0.25)
INSERT INTO @TempTable(ID, Rate, Points) VALUES ( 7, 4.875, -0.375)
INSERT INTO @TempTable(ID, Rate, Points) VALUES ( 10, 5, 0.302)
INSERT INTO @TempTable(ID, Rate, Points) VALUES ( 11, 5.125, 0.097)
INSERT INTO @TempTable(ID, Rate, Points) VALUES ( 12, 5.25, -0.241)
INSERT INTO @TempTable(ID, Rate, Points) VALUES ( 14, 5.375, -0.47)
INSERT INTO @TempTable(ID, Rate, Points) VALUES ( 16, 5.5, 0)
INSERT INTO @TempTable(ID, Rate, Points) VALUES ( 17, 5.625, -0.125)
INSERT INTO @TempTable(ID, Rate, Points) VALUES ( 18, 5.75, -0.25)
INSERT INTO @TempTable(ID, Rate, Points) VALUES ( 19, 5.875, -0.375)
INSERT INTO @TempTable(ID, Rate, Points) VALUES ( 20, 6, -0.5)
INSERT INTO @TempTable(ID, Rate, Points) VALUES ( 21, 6.25, -0.625)

At the beginning as the rate goes up, the points go down. Until you get to row 5 (ID 10) in which case it's Points are more than the previous row's ponts. What I need to do is write a query (or queries) that find this break point (for lack of a better term). Once I have the ID of where the data goes haywire, then I'll just delete all the bad rows - any row that comes after the breakpoint.

An even better solution would delete the row at ID 10, ID 11, ID 12, keep ID 14 since -.47 is less than -.375, then delete ID's 16,17, 19, but keep 20 and 21.

I feel that this is possible without a cursor - it would take multiple queries I think, and I have a hunch it's possible the answer is just eluding me.

Your thoughts please.
Thanks.

P.S. This is in SQL server 2000
 
I don't have QA in front of me, but you're gonna want to do something like this-


EDIT: my bad. I wrote this while eating dinner and I see you're not looking to find out where the IDs get messed up, but rather a different field. Yay for multitasking.

SELECT (T.ID+1) AS BAD_ID
FROM
@TempTable T
LEFT OUTER JOIN @TempTable T2 ON T.ID+1=T2.ID AND T.POINTS<T2.POINTS
WHERE
T2.ID IS NULL

As I said- I don't have QA in front of me, so you may need to tweak it, but that's the basics
 
Whats with all the hatin on cursors, sigh?
If the above query works, you should be able to loop it until it doesn't find any results.
 
pre1014 said:
Whats with all the hatin on cursors, sigh?
If the above query works, you should be able to loop it until it doesn't find any results.
Click to expand...

Cursors have their place, but they're often used incorrectly by people that understand programming concepts well (like loops in this case) well, but SQL poorly.

An example is where you could write a single update statement using joins and such (the scary kind of updates as I call 'em), but instead use a cursor to update one record at a time since you know how to loop, but don't know how to (or even that you can) write an update statement using joins.... don't laugh. I've seen it.
 
Here's a solution I believe. A couple of self joins and two queries with two local variables. This can be reduced to one I'm sure but no cursors or loops:

--------------------------------------------------------------------------------------------------
CREATE TABLE DataPoints(
ID INT
, Rate REAL
, Points REAL
)


INSERT INTO DataPoints(ID, Rate, Points) VALUES ( 1, 4.5, 0.5)
INSERT INTO DataPoints(ID, Rate, Points) VALUES ( 3, 4.625, 0.125)
INSERT INTO DataPoints(ID, Rate, Points) VALUES ( 5, 4.75, -0.25)
INSERT INTO DataPoints(ID, Rate, Points) VALUES ( 7, 4.875, -0.375)
INSERT INTO DataPoints(ID, Rate, Points) VALUES ( 10, 5, 0.302)
INSERT INTO DataPoints(ID, Rate, Points) VALUES ( 11, 5.125, 0.097)
INSERT INTO DataPoints(ID, Rate, Points) VALUES ( 12, 5.25, -0.241)
INSERT INTO DataPoints(ID, Rate, Points) VALUES ( 14, 5.375, -0.47)
INSERT INTO DataPoints(ID, Rate, Points) VALUES ( 16, 5.5, 0)
INSERT INTO DataPoints(ID, Rate, Points) VALUES ( 17, 5.625, -0.125)
INSERT INTO DataPoints(ID, Rate, Points) VALUES ( 18, 5.75, -0.25)
INSERT INTO DataPoints(ID, Rate, Points) VALUES ( 19, 5.875, -0.375)
INSERT INTO DataPoints(ID, Rate, Points) VALUES ( 20, 6, -0.5)
INSERT INTO DataPoints(ID, Rate, Points) VALUES ( 21, 6.25, -0.625)


DECLARE @point REAL
DECLARE @id INT

SELECT TOP 1 @point=T_1.Points,@id=T_1.ID
FROM
DataPoints T_0
LEFT OUTER JOIN DataPoints T_1 ON T_0.ID=(SELECT MAX(ID) FROM DataPoints TX1 WHERE TX1.ID<T_1.ID)
LEFT OUTER JOIN DataPoints T_2 ON T_1.ID=(SELECT MAX(ID) FROM DataPoints TX2 WHERE TX2.ID<T_2.ID)
WHERE T_0.Points>T_1.Points AND T_1.Points<T_2.Points

DELETE FROM DataPoints WHERE (ID>@id) AND (ID>@id AND Points>=@point)
--------------------------------------------------------------------------------------------------

In addition to the rows you mentioned this also deletes row ID 18 which I believe is correct, I'm assuming that you accidentally left it out of your list.
 
CREATE TABLE DataPoints(
ID INT
, Rate REAL
, Points REAL
)


INSERT INTO DataPoints(ID, Rate, Points) VALUES ( 1, 4.5, 0.5)
INSERT INTO DataPoints(ID, Rate, Points) VALUES ( 3, 4.625, 0.125)
INSERT INTO DataPoints(ID, Rate, Points) VALUES ( 5, 4.75, -0.25)
INSERT INTO DataPoints(ID, Rate, Points) VALUES ( 7, 4.875, -0.375)
INSERT INTO DataPoints(ID, Rate, Points) VALUES ( 10, 5, 0.302)
INSERT INTO DataPoints(ID, Rate, Points) VALUES ( 11, 5.125, 0.097)
INSERT INTO DataPoints(ID, Rate, Points) VALUES ( 12, 5.25, -0.241)
INSERT INTO DataPoints(ID, Rate, Points) VALUES ( 14, 5.375, -0.47)
INSERT INTO DataPoints(ID, Rate, Points) VALUES ( 16, 5.5, 0)
INSERT INTO DataPoints(ID, Rate, Points) VALUES ( 17, 5.625, -0.125)
INSERT INTO DataPoints(ID, Rate, Points) VALUES ( 18, 5.75, -0.25)
INSERT INTO DataPoints(ID, Rate, Points) VALUES ( 19, 5.875, -0.375)
INSERT INTO DataPoints(ID, Rate, Points) VALUES ( 20, 6, -0.5)
INSERT INTO DataPoints(ID, Rate, Points) VALUES ( 21, 6.25, -0.625)


DECLARE @point REAL
DECLARE @id INT

SELECT TOP 1 @point=T_1.Points,@id=T_1.ID
FROM
DataPoints T_0
LEFT OUTER JOIN DataPoints T_1 ON T_0.ID=(SELECT MAX(ID) FROM DataPoints TX1 WHERE TX1.ID<T_1.ID)
LEFT OUTER JOIN DataPoints T_2 ON T_1.ID=(SELECT MAX(ID) FROM DataPoints TX2 WHERE TX2.ID<T_2.ID)
WHERE T_0.Points>T_1.Points AND T_1.Points<T_2.Points

DELETE FROM DataPoints WHERE (ID>@id) AND (ID>@id AND Points>=@point)
Click to expand...

I learn something new everyday. I've spent the last 10 minutes looking at your delete statment thinking about how it decides to delete the erroneous rows. After seeing it - DOH! I knew it was straight forward but didn't hit on the using the ID >@id in both sides of the and clause.

Your solution is much simpler than mine - I came up with using a couple temp tables and a while loop to renumber the id's... A lot more steps and a TON more iterations. Not a huge problem because the dataset is tiny, but certainly not optimal.

I don't hate cursors and loops, I've just found that forcing myself to think about doing things as set operations causes me to be a better SQL coder. There have been times where I've used a cursor becasue there was NO other way. This time I had a really strong hunch there was a way to do with with a set operation, but couldn't come up with the right criteria for the where clause. Using a set operation also tends to be more elegant, faster, and usually (IMHO) has fewer errors.

Thanks! Nicely done Heatlesssun
 
Bohica69 said:
I learn something new everyday. I've spent the last 10 minutes looking at your delete statment thinking about how it decides to delete the erroneous rows. After seeing it - DOH! I knew it was straight forward but didn't hit on the using the ID >@id in both sides of the and clause.

Your solution is much simpler than mine - I came up with using a couple temp tables and a while loop to renumber the id's... A lot more steps and a TON more iterations. Not a huge problem because the dataset is tiny, but certainly not optimal.

I don't hate cursors and loops, I've just found that forcing myself to think about doing things as set operations causes me to be a better SQL coder. There have been times where I've used a cursor becasue there was NO other way. This time I had a really strong hunch there was a way to do with with a set operation, but couldn't come up with the right criteria for the where clause. Using a set operation also tends to be more elegant, faster, and usually (IMHO) has fewer errors.

Thanks! Nicely done Heatlesssun
Click to expand...

Happy to help. I like "can I do it without cursors" SQL problems.

Actually the WHERE clause has a redundant comparison. I fixed that and turned into just one query:

DELETE DataPoints WHERE ID IN
(
SELECT DataPoints.ID
FROM DataPoints, (SELECT TOP 1 T_1.Points,T_1.ID
FROM
DataPoints T_0
LEFT OUTER JOIN DataPoints T_1 ON T_0.ID=(SELECT MAX(ID) FROM DataPoints TX1 WHERE TX1.ID<T_1.ID)
LEFT OUTER JOIN DataPoints T_2 ON T_1.ID=(SELECT MAX(ID) FROM DataPoints TX2 WHERE TX2.ID<T_2.ID)
WHERE T_0.Points>T_1.Points AND T_1.Points<T_2.Points) TP
WHERE DataPoints.ID>TP.ID AND DataPoints.Points>=TP.Points
 
You must log in or register to reply here.
Back
Top