Select statement

I have an table that holds information about invoice payment transactions. It has a contract number; the contract number can be used by a particular vendor between several different locations. And each location the contract is used is recorded in the same table.(I know that is not best way to set that up, but it was done by someone else and has been in place for over two years). I want to know how can I do a select that returns the first record found only. For example,
let's say that 12345 is a contract number and the vendor has used the contract number at location A and that same contract number(12345) is used at location G, K, L, and P. I want my query to return the first record found, and ignore the rest. I can't seem to find out how to do this. I know in SQLServer you can specify maxrows, is there a function like that in Oracle?//

Thanks

What is the criteria for deciding the 'first' occurance of the record ? Is there any date or serial number recorded in the table ? If that is the case, it should be simple.

There isn't a date or serial number tied to it, that is why I want the first occurence only. It does not matter, if I get a record that was inserted last or first, I just want the first occurence.

"FIRST" on what basis ? Or do you mean to say "ONE" ?

Try something like this:

Select contract_number, location
from TABLE_NAME where rownum < 2;

This will return only one row.

Make use of ROWNUM. For example,

Select contract_num, location
from your_table
where contract_num=12345
and rownum=1;

Ok....let me go into a little more detail....
These are columns of my table
ContractNumber | CCode| DCode| LocationCode
12345 36343 38904 93283
12345 36343 38904 83943
12345 36343 38904 02932
25334 89209 17323 92022
25334 89209 17323 93742
83930 83849 88343 90283
83930 83849 88343 02833
...

I know this terrible, but this what the data looks in the table and what I have to work with. I need to select all the contract numbers from this table( about 100000 rows), but I only want the first occurence. Rownum will not work because I need all the contract numbers and if I use rownum it will return only one record. I have tried DISTINCT but it still returns all occurences of the contract number. This would probably be better handled through PL/SQL, but I wanted to make sure I could not do it through SQL first.

Thanks for your help.

I must agree with the various people that have taken issue with the terminology. 'First' implies an order. 'One' does not.

So, basically, you want 1 (random) record for a given contract id. For that, ocp_dev's code will work fine.

If, however, you want 1 (random) record for multiple contract ids, then you will need a slightly different solution.

For 8.1.6.2 or later...

Code:

---

SELECT
  CONTRACT_NUM,
  LOCATION     
FROM
  (
  SELECT
      CONTRACT_NUM,
      LOCATION      ,
      ROW_NUMBER() 
        OVER
        (
            PARTITION BY
              CONTRACT_NUM
        )
        AS  RN
  FROM
      YOUR_TABLE
  )
WHERE
  RN = 1;

---

HTH,

- Chris

I apologize for the use of "first" that can be misleading...but you are correct Chris, I just want one random record. Unfortunately, the database I am selecting from is v7.3.4. Any other suggestions, or will this be better solved using PL/SQL?//

Thanks...and again I apologize for the confusion.

What columns do you want to select ? Is it only contract_number ? If yes, then why DISTINCT doesn't work ?

If you want to select other columns also, how about this :

select contract_number,col_1,col_2,col_3,...,col_n
from the_table a
where rowid=(select min(rowid)
from the_table
where contract_number=a.contract_number);

This would be more appropriate in the development forum rather than being in the administration. So I'm taking the liberty to move this thread to the development forum from the administration forum


Sam

While I applaud the general approach of the solution, I would argue against its usage of ROWID. IMHO, ROWID should never be used in production SQL.

I might suggest the following instead:

select contract_number,col_1,col_2,col_3,...,col_n
from the_table a
where CCode || ' ' || DCode || ' ' || LocationCode
=(select min(CCode || ' ' || DCode || ' ' || LocationCode )
from the_table
where contract_number=a.contract_number);

Anyway, just a thought,

- Chris