Row movement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Row movement

Author

Topic

Listen
Starting Member

44 Posts

Posted - 2004-03-18 : 01:16:28

Hi there,

Can I move a row that is located below and re-locate it in first or second row using update? I have no index.

Sample:


RowID Column2  Column3
  1      500     F
  2      700     F
  3     1100     T

Correct Result:
RowID Column2  Column3
  1      1100    T
  2       500    F
  3       700    F

Notice that the record number 3 moved to record number 1 because
records the 1100 has T value.

Thanks.

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2004-03-18 : 01:39:04

Sure you can "move" the row by running two update queries. The first query sets the values of Column 2 and 3 of Row 3 to the respective values of Row 1 and then do the reverse to "move" the old Row 1 down.

But this begs the question, why does the physical ordering of the data in the database matter? You should be able to store rows/columns in any order within the table/record and use SQL to manipulate the order/position of the data.

Justin

"I want to thank you. You could've given us help, but you've given us so much more."

Listen
Starting Member

44 Posts

Posted - 2004-03-18 : 01:42:44

Actually the sample records I sent were just an illustration. The real application has a lot of records to move on the upper row if Column3 is T (True). I need a sample code to update the said table.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-03-18 : 03:02:05

Ok, so if your table had say, ten records with column3 = T, then what rule is applied to decide which row goes to RowID 1, which to 2, etc.? I don't really like the idea of moving rows around as if this was Excel, in an RDBMS you should be able to sort data in many more ways. Maybe if you tell us what is the business logic behind what you are trying to, we could suggest a better way to do it in a SELECT query?

OS

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2004-03-18 : 03:02:55

Well, still Justins' question is valid.
Ordering in a RDBMS is meaningless and irrelevant without using an ORDER BY.
If you need some kind of sequential numbering you might find this useful
http://support.microsoft.com:80/support/kb/articles/q186/1/33.asp&NoWebContent=1
But there is no need to store these informations in the database.

--Frank
http://www.insidesql.de

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-03-18 : 09:10:57

select *
from mytable
order by column3 desc

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2004-03-18 : 09:14:15

Not quite right.
That way he won't get this nice


1
2
3

thingy

--Frank
http://www.insidesql.de

Hmpfh,...and the apparently ascending order of Column2..

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-03-18 : 10:42:18

I thought so too for Column2 but figured the poster could do it.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-03-18 : 19:01:51

Here is your sample code, but please take notice of all the previous posts.... It only works if Column2 and Column3 form a unique constraint.....


create table Garbage(RowID INT NOT NULL, Column2 INT, Column3 CHAR(1))
GO
insert Garbage
	SELECT 1, 500, 'F'
	UNION ALL
	SELECT 2, 700, 'F'
	UNION ALL
	SELECT 3, 1100, 'T'
GO
--A Select
SELECT (SELECT COUNT(*) FROM Garbage X WHERE X.Column3 >= G.Column3 AND X.Column2 >= G.Column2) AS ROWID, Column2, Column3
FROM garbage G
ORDER BY ROWID
GO
--To make permenant
Update G
	SET RowID = (SELECT COUNT(*) FROM Garbage X WHERE X.Column3 >= G.Column3 AND X.Column2 >= G.Column2)
FROM Garbage G
GO
--You will still need to order it!!!!!
Select * 
from Garbage
ORDER BY ROWID
GO
drop table Garbage

DavidM

"An Ugg Boot is a generic Australian term that has been in use for nearly 100 hundred years. Now some coporate wanker has trademarked it.. "

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources