order by column in table structure not in result set

[aleem510 via SQLMonster.com]

hi,
i have a table with 500+ records and they got jumble while inserting . now i
want to arrange them in order by column name in the table structure not in
result set..

i want somthing that works like MYSQL (Alter table order by column name)

Thanks & regards:
Aleem

--
Message posted via http://www.sqlmonster.com

[John Bell]

On Wed, 25 Aug 2010 05:23:42 GMT, "aleem510 via SQLMonster.com"
<u61699@uwe> wrote:

>hi,
>i have a table with 500+ records and they got jumble while inserting . now i
>want to arrange them in order by column name in the table structure not in
>result set..
>
>i want somthing that works like MYSQL (Alter table order by column name)
>
>
>Thanks & regards:
>Aleem

Hi Aleem

I am not sure what you mean by jumbled up. The order of the columns
will be the same as the order you created the table. If the table was
created as the result of a SELECT..INTO change the order of the
columns in the SELECT clause and your table will have been created in
that order.

SELECT * is considered bad practice, if you want to change the order
so that you don't have to specify a column lists then you need to drop
the idea! You should only be selecting what you want and then column
order will be what you specify.

Just because MYSQL behaves that way doesn't mean that other systems
will, and if you want you application to be portable then don't rely
on features that are not standard. It could be that future releases of
MYSQL may not behave that way.

John

[aleem510 via SQLMonster.com]

i appreciate ur reply. but my question is
for example:
i have emp id's from 1 to 100 but the order they got saved is 1 to 40 then i
modified 22nd record now it started inserting 41st record after 22nd instead
of inserting after 40th.....
so i need to change there order in table not in result bcoz i had already
written around 30 store procedures on that..so dey r coming like

1,2,3,4.......21,22,41,42,43,...81,82,23,24......39,40.

John Bell wrote:
>>hi,
>>i have a table with 500+ records and they got jumble while inserting . now i

>[quoted text clipped - 5 lines]

>>Thanks & regards:
>>Aleem
>Hi Aleem
>
>I am not sure what you mean by jumbled up. The order of the columns
>will be the same as the order you created the table. If the table was
>created as the result of a SELECT..INTO change the order of the
>columns in the SELECT clause and your table will have been created in
>that order.
>
>SELECT * is considered bad practice, if you want to change the order
>so that you don't have to specify a column lists then you need to drop
>the idea! You should only be selecting what you want and then column
>order will be what you specify.
>
>Just because MYSQL behaves that way doesn't mean that other systems
>will, and if you want you application to be portable then don't rely
>on features that are not standard. It could be that future releases of
>MYSQL may not behave that way.
>
>John

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/201008/1

[Erland Sommarskog]

aleem510 via SQLMonster.com (u61699@uwe) writes:
> i appreciate ur reply. but my question is
> for example:
> i have emp id's from 1 to 100 but the order they got saved is 1 to 40
> then i modified 22nd record now it started inserting 41st record after
> 22nd instead of inserting after 40th.....
> so i need to change there order in table not in result bcoz i had already
> written around 30 store procedures on that..so dey r coming like
>
> 1,2,3,4.......21,22,41,42,43,...81,82,23,24......39,40.

You can't do that. You can't change something which does not exist. That is,
a table is an unordered set of data.

It's fundamental in SQL that if you want a certain order of a query, you
need to use an ORDER BY clause.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx