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?//
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.
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?//