Results 1 to 8 of 8
  1. #1
    Join Date
    May 2004
    Posts
    29

    Unanswered: How to get the data from the first one?

    How can the following sql be modified to only select the ONLY one that actually contain data I am looking for. I am looking for column that may contain source_type = 'F'.

    My syntax is simple but couldn't find the way how to make it pull the first souce_type='F' out

    Select * from xxxx where source_type ='F' and ....????

    Please help. Thanks much.

    BV

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking


    You could use ROWNUM:
    Code:
    Select * from xxxx where source_type ='F' and ROWNUM=1;


    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    May 2004
    Posts
    29
    I have tried this already and the data is blank.
    since there are so many type 'D', 'F', 'S', and so on .... if rownum =1 it give you the first record w/o data when source_type ='F' is not on the first record, it may be on 5th, 10, 11, etc...

  4. #4
    Join Date
    Oct 2004
    Location
    Oklahoma City OK -
    Posts
    122
    Does the data have a column containing date inserted?

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down

    Quote Originally Posted by bvo
    I have tried this already and the data is blank.
    since there are so many type 'D', 'F', 'S', and so on .... if rownum =1 it give you the first record w/o data when source_type ='F' is not on the first record, it may be on 5th, 10, 11, etc...
    Wrong, it should return the first source_type='F' that it finds!!!
    Code:
    SQL>select employee_id, last_name||', '||first_name name, department_id
      2  from employees
      3*where department_id='30';
    
    
    EMPLOYEE_ID NAME                                            DEPARTMENT_ID
    ----------- ----------------------------------------------- -------------
            114 Raphaely, Den                                              30
            115 Khoo, Alexander                                            30
            116 Baida, Shelli                                              30
            117 Tobias, Sigal                                              30
            118 Himuro, Guy                                                30
            119 Colmenares, Karen                                          30
    
    6 rows selected.
    
    SQL>select employee_id, last_name||', '||first_name name, department_id
      2  from employees
      3*where department_id='30' AND ROWNUM=1;
    
    EMPLOYEE_ID NAME                                            DEPARTMENT_ID
    ----------- ----------------------------------------------- -------------
            114 Raphaely, Den                                              30
    
    SQL>
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Dec 2003
    Posts
    1,074
    I think you're looking for the first 'F', the first 'D', etc?
    -cf

  7. #7
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    You cannot do rownum=n in the query above and expect to get the nth record in the resultset as you dont know how Oracle is going to retrieve the records as that will depend on the execution plan.

    Use this

    select * from
    (
    select * from tablex where source_type=x order by y
    ) where rownum=1

    This forces Oracle to retrieve your resultset first (in a defined order) and then afterwards assign rownum, instead of assigning rownum first and then checking for source_type. You can also do it with the row_number() analytic function. See what works best in your environment.

    Alan

  8. #8
    Join Date
    May 2004
    Posts
    29
    Thanks so much... I made it work on Friday and my query is the same as AlanP.

    Thank you ... thank you much....... Have a safe and happy Holidays

    BV

Posting Permissions

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