Select statement
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Select statement

Hybrid View

  1. #1
    Join Date
    Apr 2001
    Posts
    42
    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
    Utep
    Share on Google+

  2. #2
    Join Date
    Jul 2000
    Posts
    521
    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.
    svk
    Share on Google+

  3. #3
    Join Date
    Apr 2001
    Posts
    42
    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.
    Utep
    Share on Google+

  4. #4
    Join Date
    Jul 2000
    Posts
    521
    "FIRST" on what basis ? Or do you mean to say "ONE" ?
    svk
    Share on Google+

  5. #5
    Join Date
    Feb 2001
    Posts
    27
    Try something like this:

    Select contract_number, location
    from TABLE_NAME where rownum < 2;

    This will return only one row.
    Share on Google+

  6. #6
    Join Date
    Aug 2001
    Posts
    20
    Make use of ROWNUM. For example,

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

    Share on Google+

  7. #7
    Join Date
    Apr 2001
    Posts
    42
    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.
    Utep
    Share on Google+

  8. #8
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong
    Share on Google+

  9. #9
    Join Date
    Apr 2001
    Posts
    42
    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.
    Utep
    Share on Google+

  10. #10
    Join Date
    Jul 2000
    Posts
    521
    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);

    svk
    Share on Google+

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width