Home » SQL & PL/SQL » SQL & PL/SQL » sql query
sql query [message #340216] Tue, 12 August 2008 00:37 Go to next message
gaikwadrachit
Messages: 33
Registered: June 2007
Location: mumbai
Member
hi

I am having a column named sequence which has datatype number.
it has 10 records in it.it is not a primary key.

eg
sequence name
----------- -----------
1 a
2 bb
3 cccc
4 dd
5 eee
6 fff
7 h
8 i

now the problem is that due to some scripts the sequence are repeating .for eg

sequence name
----------- -----------
1 a
2 bb
3 cccc
4 dd
4 eee
5 fff
5 h
8 i

i know that i can due it manually but what if the records are too large...

i want to write a query by which the column sequence will rearrange in proper order.

thanks in advanced
Re: sql query [message #340217 is a reply to message #340216] Tue, 12 August 2008 00:38 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above
Re: sql query [message #340219 is a reply to message #340217] Tue, 12 August 2008 00:48 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Quote:

i want to write a query by which the column sequence will rearrange in proper order.



What do you mean by this??

Do you want to remove duplicates??

Regards,
Rajat
Re: sql query [message #340222 is a reply to message #340219] Tue, 12 August 2008 00:53 Go to previous messageGo to next message
gaikwadrachit
Messages: 33
Registered: June 2007
Location: mumbai
Member
aree i want to remove duplicate but the series should be

1
2
3
4
5
6
7
8
9
10
11
Re: sql query [message #340224 is a reply to message #340222] Tue, 12 August 2008 00:56 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Please serach the forum this question has been asked so many times.

Wiki also has detailed description of doing this.

Try some google.

Next time please search on google before posting it here.

Regards,
Rajat
Re: sql query [message #340226 is a reply to message #340216] Tue, 12 August 2008 00:58 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Quote:

now the problem is that due to some scripts the sequence are repeating



Make the change in the script with sequence.nextval where sequence value is inserting in the table.
Re: sql query [message #340227 is a reply to message #340216] Tue, 12 August 2008 00:59 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Of those multiple records with id 4, which should come first?
In other words, what is the exact way you want the final set of records to be ordered?
Re: sql query [message #340235 is a reply to message #340216] Tue, 12 August 2008 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
now the problem is that due to some scripts the sequence are repeating...

I bet some stupid script that use max or count to get the next number.

Now as Frank pointed, no this can't be done in a deterministic way unless you have some more information.

Regards
Michel
Re: sql query [message #340241 is a reply to message #340227] Tue, 12 August 2008 01:21 Go to previous messageGo to next message
gaikwadrachit
Messages: 33
Registered: June 2007
Location: mumbai
Member
This is the final output i should get..........

1 a
2 bb
3 cccc
4 dd
5 eee
6 fff
7 h
8 i
Re: sql query [message #340245 is a reply to message #340241] Tue, 12 August 2008 01:33 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why?
Why is that id 4 the one that gets 4 instead of 5?
Previous Topic: Help Needed in selecting the values from PL/SQL Table and Unable to Bulk Collect in PLSQL table (2 t
Next Topic: How to fatch the data from previous row
Goto Forum:
  


Current Time: Fri Dec 09 23:17:20 CST 2016

Total time taken to generate the page: 0.05217 seconds