Home » SQL & PL/SQL » SQL & PL/SQL » retrieving the min not existing value in a column of a table (feature server, LINUX)
retrieving the min not existing value in a column of a table [message #351227] Tue, 30 September 2008 00:54 Go to next message
prathiba
Messages: 5
Registered: September 2008
Location: bangalore
Junior Member

consider a table:
number < unique int > has a range < 1 - 65535 >
name < need not be unique >

now, when the table is:
1, A
2, B
3, A
4, C
5, E
6, A

now when i delete 2nd and 5 th row.
it looks like:
1, A
3, A
4, C
6, A

Now the query i want should result in
2
meaning: i need to fetch the non existing value as compared against the number which is min.

It will be helpful if you could help me in this regard.
Thank you,
Prathiba Smile

Re: retrieving the min not existing value in a column of a table [message #351230 is a reply to message #351227] Tue, 30 September 2008 00:58 Go to previous messageGo to next message
prathiba
Messages: 5
Registered: September 2008
Location: bangalore
Junior Member

when i discussed with my seniour, he said somethings if we cud use MINUS, RANGE and a NOT EXISTS. or shud we use any LOOP and funcions...
Re: retrieving the min not existing value in a column of a table [message #351234 is a reply to message #351227] Tue, 30 September 2008 01:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's a clue:
SQL> create table t as select distinct round(dbms_random.value(1,10)) seq from dual connect by level < 10;

Table created.

SQL> select * from t order by 1;
       SEQ
----------
         2
         3
         4
         6
         7
         9

6 rows selected.

SQL> select level from dual connect by level <= 10
  2  minus
  3  select seq from t
  4  order by 1
  5  /
     LEVEL
----------
         1
         5
         8
        10

4 rows selected.

Regards
Michel
Re: retrieving the min not existing value in a column of a table [message #351250 is a reply to message #351227] Tue, 30 September 2008 01:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why is 5 not in your desired resultset? Do you just want a count of missing rows?
Re: retrieving the min not existing value in a column of a table [message #351255 is a reply to message #351250] Tue, 30 September 2008 02:05 Go to previous messageGo to next message
prathiba
Messages: 5
Registered: September 2008
Location: bangalore
Junior Member

i want a SQL query which results in MIN missing 'number' ( within int range of 1- 65535)

2 and 5 are missing. but min of them is 2. so result i want is 2.
Re: retrieving the min not existing value in a column of a table [message #351257 is a reply to message #351255] Tue, 30 September 2008 02:08 Go to previous messageGo to next message
prathiba
Messages: 5
Registered: September 2008
Location: bangalore
Junior Member

to be more clear.
missing numbers here are 2, 5, 7,8,9,10,... 65534, 65535.
but min of this set is 2.
Re: retrieving the min not existing value in a column of a table [message #351258 is a reply to message #351255] Tue, 30 September 2008 02:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And you didn't find an answer from what I posted?

Regards
Michel
Re: retrieving the min not existing value in a column of a table [message #351260 is a reply to message #351227] Tue, 30 September 2008 02:19 Go to previous messageGo to next message
idris.ali
Messages: 34
Registered: June 2008
Location: Hyderabad
Member
Hi,

You may use rownum along with order by

like

SELECT ROWNUM /* + offset */ what_it_should_be , seq what_it_is
FROM t ORDER BY seq

(add offset if the numbers don't start from 1)

the first non matching values in what_it_should_be and what_it_is
gives u the result.
Re: retrieving the min not existing value in a column of a table [message #351271 is a reply to message #351260] Tue, 30 September 2008 03:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is:
- unreadable
- ununderstandable
- wrong

In addition "u" is not a member of OraFAQ and so will not give anything.

Regards
Michel
Re: retrieving the min not existing value in a column of a table [message #351324 is a reply to message #351271] Tue, 30 September 2008 05:25 Go to previous messageGo to next message
prathiba
Messages: 5
Registered: September 2008
Location: bangalore
Junior Member

i think ROW_NUMBER() OR ROWNUM is not supported in timesten. Sad
because
SELECT ROW_NUMBER() OVER (ORDER BY number ASC) AS ROWID, * FROM tables;
1001: Syntax error in SQL statement before or at: "(", character position: 19
The command failed.
Re: retrieving the min not existing value in a column of a table [message #351327 is a reply to message #351324] Tue, 30 September 2008 05:31 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition ROWID is a reserved word.

Regards
Michel
Previous Topic: convert a number to money format with $
Next Topic: How to get last compiled version of package.?
Goto Forum:
  


Current Time: Wed Dec 07 20:23:43 CST 2016

Total time taken to generate the page: 0.06607 seconds