| Author |
Topic |
|
scullee
Posting Yak Master
103 Posts |
 Posted - 2003-03-23 : 22:24:56
|
| Im currently porting an oracle database to SQL server and are running into all sorts of problems with dodgy data in the system.The system is used for a car alarm which interacts with a call centre when the car is stolen etc. The system has a list of phone numbers to call to find the owner. And this has a priority. Well the current system has a swag of duplicate priorities. Since the CarID (VIN) and the Priority is my Key in the new table this obviously wont work.I have of the records in an intermediate SQL Server table to clean it up and i want to remove the data.eg Say Car 123 has 5 phone recordsCarID Rank Phone123 1 03 98787787123 1 03 98898987123 2 03 98985457123 3 03 98756445I want the new data to look likeCarID Rank Phone123 1 03 98787787123 2 03 98898987123 3 03 98985457123 4 03 98756445I really dont care which of the 1 ones gets moved up to a 2 but i want the rest of the rank to be in the same order.The only way i can think of to do this is with a cursor but i am wondering if there is a better way.Any ideas? |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-03-24 : 01:29:01
|
| scullee,I hate to say this but...This will be one of those times when a cursor will be better than a set based solution.Someone please prove me wrong...DavidM"SQL-3 is an abomination.." |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-03-24 : 07:29:53
|
| HAHAHAHAHAHAHAHAHA, OK David:CREATE #temp (CarID int NOT NULL, Rank int NOT NULL, Phone varchar(20) NOT NULL)INSERT INTO #temp(CarID, Rank, Phone) SELECT CarID, Rank, Phone FROM myTableCREATE CLUSTERED INDEX Temp_Car_Rank ON #temp(CarID, Rank)DECLARE @rank int, @car intSET @rank=1UPDATE #tempSET @rank=Rank=CASE @car WHEN CarID THEN @rank+1 ELSE 1 END, @car=CarIDThis will renumber the ranks properly, but it will start each new sequence at 1, even if there isn't an existing rank of 1. Of course you can do this with a regular table instead of a temp table if you want to skip an intermediate step. |
|
|
|
scullee
Posting Yak Master
103 Posts |
Posted - 2003-03-24 : 20:17:49
|
| Well im impressed now. I was under the impression cursors is the only way to do this correctly and the other database guys around here said the same thing.I wasnt aware that you could use variables in a select like that but hey you learn something new every day.I do have one question about the solution. Is the create index there to make sure that you get the records in the correct order or is that done some other way that im missing? |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-03-24 : 20:50:42
|
Rob,I was going to suggest that method except for...quote:
but i want the rest of the rank to be in the same order.
You cheated by adding the CLUSTERED INDEX!!!EDIT: Scullee, this problem is "almost"* impossible without a guareenteed ordering...That is why Rob has added the CLUSTERED INDEX.*I still stand by my original post unless someone else has another solution that does not require a CLUSTERED INDEXDavidM"SQL-3 is an abomination.."Edited by - byrmol on 03/24/2003 20:53:50 |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-03-24 : 22:06:39
|
Cheat????? MOI???? The challenge was to do it without cursors, and I did. And I'll bet it performs better than a cursor too, even with the clustered index being built.And it's not "almost" impossible without guaranteeing ordering...it IS impossible without it. The rank is adding an order to the rows that holds significance. Edited by - robvolk on 03/24/2003 22:07:30 |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-03-25 : 00:28:42
|
quote:
And I'll bet it performs better than a cursor too, even with the clustered index being built.
That was great lateral thinking Rob and without a shadow of doubt will blow the cursor away...DavidM"SQL-3 is an abomination.." |
|
|
|
scullee
Posting Yak Master
103 Posts |
Posted - 2003-03-25 : 00:45:13
|
| The ordering was critical.Just for future knowledge, will an update always scan through with an order based on a clustered index in this situation?I was always under the impression with a relational database you couldnt guarantee the order unless you had an order by clause. |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-03-25 : 01:28:10
|
| Thats right, scullee, there is no such thing as a row order in an RDBMS. The exception (if you can call it that) is the result from a table with a clustered index, which differs from other indexes in that it affects the physical order of the data in the index. And that is why you cannot have more than one clustered index on a table.OS |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-03-25 : 01:47:44
|
Ok Rob..I think I have a solution.....I original said "almost impossible" because I noticed that the Phone Number is different for each Car-Rank combination and felt that that was a basis for ordering....So without further ado...CREATE TABLE Cars (CarID int NOT NULL , Rank int NOT NULL ,Phone char(20) NOT NULL)GOINSERT Cars (CarID, Rank, Phone)SELECT 123, 1,'03 98787787'UNION ALLSELECT 123, 1,'03 98324987'UNION ALLSELECT 123, 2,'03 98985457'UNION ALLSELECT 123, 3,'03 98756445'UNION ALLSELECT 323, 1,'03 98787387'UNION ALLSELECT 323, 2,'03 98823487'UNION ALLSELECT 323, 2,'03 98985467'UNION ALLSELECT 323, 3,'03 98754445'GOUPDATE CSET Rank = CASE WHEN (SELECT COUNT(*) FROM Cars WHERE CarID = C.CarID and Rank < C.Rank) + 1 < (SELECT Count(*) FROM Cars WHERE CarID = C.CarID and Phone < C.Phone)THEN (SELECT Count(*) FROM Cars WHERE CarID = C.CarID and Phone < C.Phone)ELSE (SELECT Count(*) FROM Cars WHERE CarID = C.CarID and Rank < C.Rank) + 1 ENDFROM Cars CGO Sorry about the width....DavidM"SQL-3 is an abomination.." |
|
|
|
|
Repairing Dodgy Data / Re keying tables