July 20, 2005 at 10:23 am
Hi
Once the table is populated with data say id,name
id | char
1 A
2 B
3 C
4 D
Is there a way we can reorder these contents with in the table...which could look like
2 B
4 D
1 A
3 C
or something like that.
Thanks
July 20, 2005 at 11:06 am
What are you trying to accomplish with this? Relational tables typically don't have an 'internal order'. You have to use the 'order by' clause when you retrieve the data to guarantee a specific ordering. If you want these to come back in some predictable order, you need to add a column with a set of values you can order by.
July 20, 2005 at 11:24 am
If there is a clustered index the rows will be in the order of the clustered index if not it will be in the order the data is inserted. See the difference between TableA and TableB.
Either way we don't have control as David pointed. The order cannot be changed. Use order by to retrive the data.
SET NOCOUNT ON
DECLARE @TableA TABLE
(
RowNum INT PRIMARY KEY CLUSTERED,
RowName VARCHAR(1)
)
INSERT INTO @TableA VALUES (2, 'B')
INSERT INTO @TableA VALUES (4, 'D')
INSERT INTO @TableA VALUES (1, 'A')
INSERT INTO @TableA VALUES (3, 'C')
SELECT * FROM @TableA
DECLARE @TableB TABLE
(
RowNum INT,
RowName VARCHAR(1)
)
INSERT INTO @TableB VALUES (2, 'B')
INSERT INTO @TableB VALUES (4, 'D')
INSERT INTO @TableB VALUES (1, 'A')
INSERT INTO @TableB VALUES (3, 'C')
SELECT * FROM @TableB
Regards,
gova
July 20, 2005 at 12:19 pm
If there is some sort of sequence required that is not supported by the data itself, as in this case, you will have to add a third column and use it in an ORDER BY clause.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply