Home » SQL & PL/SQL » SQL & PL/SQL » Insert into table & then sort the colum value (Oracle, 9.2.0.1.0, Windows XP)
Insert into table & then sort the colum value [message #427628] Fri, 23 October 2009 11:29 Go to next message
Moksha
Messages: 16
Registered: October 2009
Junior Member
Hi!

I have a table called Countries with ID and Name columns. I have already some data in this table, now when I insert a new record then I want to update the ID value so that it is in order. For example, if the values in the table are:

ID          NAME
1           Albania
2           Algeria
3           Bahamas
4           Bahrain
5           Cambodia
6           Czech Republic
7           India
8           Zaire


Now, if I insert 9 Chile, 10 Zimbabwe, 11 South Korea then, I want to have the values in the table re-ordered sorted on the NAME ascending i.e., want to have the records look as follows:

ID          NAME
1           Albania
2           Algeria
3           Bahamas
4           Bahrain
5           Cambodia
6           Chile
7           Czech Republic
8           India
9           South Korea
10          Zaire
11          Zimbabwe


Can anyone help me how I can achieve this functionality? I am not using sequence on the table and do not want to use sequence on the table.

Need this help urgently.

Thanks,
Moksha
Re: Insert into table & then sort the colum value [message #427629 is a reply to message #427628] Fri, 23 October 2009 11:39 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10671
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
What are you going to achieve with this "functionality"?
Re: Insert into table & then sort the colum value [message #427630 is a reply to message #427629] Fri, 23 October 2009 11:44 Go to previous messageGo to next message
ThomasG
Messages: 3185
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
If the ID is used anywhere, for example in a "customers" or "employees" table, you will move the entire population from India into the Czech Republic for example, which will cause the country to shut down because of over-population.

An "ID" is something for "IDentification" never change an ID.

[Updated on: Fri, 23 October 2009 11:46]

Report message to a moderator

Re: Insert into table & then sort the colum value [message #427631 is a reply to message #427628] Fri, 23 October 2009 11:49 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I want to have the values in the table re-ordered sorted on the NAME ascending

When you think that a table has no order you realize that what you asked is plain stupid.
Or, if you prefer, the names are already ordered as soon as you realize that to have an order your MUST use an ORDER BY clause.

Regards
Michel
Re: Insert into table & then sort the colum value [message #427632 is a reply to message #427630] Fri, 23 October 2009 11:49 Go to previous messageGo to next message
BlackSwan
Messages: 24908
Registered: January 2009
Senior Member
>An "ID" is something for "IDentification" never change an ID.
except when it is part of a student's homework assignment
Re: Insert into table & then sort the colum value [message #427643 is a reply to message #427628] Fri, 23 October 2009 13:25 Go to previous messageGo to next message
Moksha
Messages: 16
Registered: October 2009
Junior Member
This is just one stand alone table and do not have any foreign key relations with other tables. This functionality is required to just to have this table in order to populate in a front-end application and if tomorrow, if any additions or deletions change then want to have the ID column updated so that the name columns are ordered & have the incremental ID or serila number.

I am just thinking as follows:
1) insert the new records in the table,
2)create a table with ID populated serially and then insert the name from the original table, then delete the original table with order by on the name.
3) Rename the newly created table to the original name.

If you think that this is not OK or if there is any better way, please let me know.

Thanks,
Moksha

[Updated on: Fri, 23 October 2009 13:45]

Report message to a moderator

Re: Insert into table & then sort the colum value [message #427646 is a reply to message #427628] Fri, 23 October 2009 13:45 Go to previous messageGo to next message
BlackSwan
Messages: 24908
Registered: January 2009
Senior Member
>If you think that this is not OK or if there is any better way, please let me know.
Do NOT store ID in the table.

SELECT ROWDID, CITY_NAME FROM TABLE1 ORDER BY CITY_NAME
Re: Insert into table & then sort the colum value [message #427647 is a reply to message #427628] Fri, 23 October 2009 13:59 Go to previous messageGo to next message
Moksha
Messages: 16
Registered: October 2009
Junior Member
Ok. I thank everyone who have responded and given valuable inputs and suggestions.


Thanks,
Moksha
Re: Insert into table & then sort the colum value [message #427657 is a reply to message #427647] Fri, 23 October 2009 15:28 Go to previous messageGo to next message
joy_division
Messages: 4616
Registered: February 2005
Location: East Coast USA
Senior Member
Moksha wrote on Fri, 23 October 2009 14:59
Ok. I thank everyone who have responded and given valuable inputs and suggestions.


However, I have a feeling you are going to ignore it and continue down the wrong path. Sometimes people are stubborn and do not know what a relational database is or think their way is better.

I implore you to do as the other have said and do not store the ID in the table. It offers no advantage as you would be sorting by the NAME.

And on a side note, BlackSwan meant ROWID, not ROWDID.
Re: Insert into table & then sort the colum value [message #427663 is a reply to message #427657] Fri, 23 October 2009 18:12 Go to previous messageGo to next message
Moksha
Messages: 16
Registered: October 2009
Junior Member
I will use this forum's response to try to convince someone. They have their own reasons for this functionality and hopefully, I can convince to follow these suggestions.

Thanks,

Moksha
Re: Insert into table & then sort the colum value [message #427676 is a reply to message #427657] Sat, 24 October 2009 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
And on a side note, BlackSwan meant ROWID, not ROWDID.

I think it was ROWNUM.

Regards
Michel
Re: Insert into table & then sort the colum value [message #427698 is a reply to message #427676] Sat, 24 October 2009 02:29 Go to previous messageGo to next message
psingh7777
Messages: 22
Registered: August 2007
Location: New Delhi
Junior Member

I dont understand if ID is of no use then why that column exists.
The best way what I feel should be Create a View

Create or replace view xxc_city_name as
select rownum,city_name from xxc_city_table
order by city_name.

Regards
Parvinder
Re: Insert into table & then sort the colum value [message #427699 is a reply to message #427698] Sat, 24 October 2009 02:41 Go to previous messageGo to next message
BlackSwan
Messages: 24908
Registered: January 2009
Senior Member
>I dont understand if ID is of no use then why that column exists.
Ask this question to the person who created the ID column.
Re: Insert into table & then sort the colum value [message #427931 is a reply to message #427676] Mon, 26 October 2009 08:00 Go to previous message
joy_division
Messages: 4616
Registered: February 2005
Location: East Coast USA
Senior Member
Michel Cadot wrote on Sat, 24 October 2009 02:01
Quote:
And on a side note, BlackSwan meant ROWID, not ROWDID.

I think it was ROWNUM.

Regards
Michel


Heh! We both made a mistake. Yep, I meant ROWNUM.
Previous Topic: All values getting populated in one cell in Excel file
Next Topic: Queue monitoring in Oracle Enterprise manager
Goto Forum:
  


Current Time: Tue Sep 27 14:31:47 CDT 2016

Total time taken to generate the page: 0.08491 seconds