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

Thread: SQL*plus Query -- Very urgent

  1. #1
    Join Date
    Jul 2001
    Posts
    334
    Hi all
    I have a data in the table, For example emp table and empno column. What I need is I want to order by empno and then select the first empno sorted empno.
    e.g:

    Before order by
    9999
    6666
    7777

    After order by
    6666
    7777
    9999

    Now I want the first sorted row only (6666) NOT (9999). What query I will write to get this result.

    Thanks


  2. #2
    Join Date
    May 2002
    Posts
    2,645
    select min(empno) from table_name;

  3. #3
    Join Date
    Jul 2001
    Posts
    334
    Not helpful, I need the solution according to my problem in original post.


    Thanks

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    select * from
    (select * from emp order by deptno) a
    where rownum = 1



    [Edited by pando on 07-18-2002 at 02:36 PM]

  5. #5
    Join Date
    Jul 2001
    Posts
    334
    Thanks Pando, Can you modify this query

    Thanks in advance,
    -------------------------------------------------
    select
    SUBSTR(ffv.flex_value,1,4) PARENT_VALUE,
    SUBSTR(ffv.description,1,25) PARENT_DESCRIPTION,
    SUBSTR(ffvh.child_flex_value_low,1,4) CHILD_LOW,
    SUBSTR(ffvh.child_flex_value_high,1,4) CHILD_HIGH
    from
    fnd_lookups fl,
    fnd_flex_hierarchies ffh,
    fnd_flex_value_hierarchies ffvh,
    fnd_flex_values ffv,
    fnd_id_flex_segments fifs
    where
    ffh.flex_value_set_id(+) = ffv.flex_value_set_id
    and ffh.hierarchy_id(+) = ffv.structured_hierarchy_level
    and ffvh.flex_value_set_id = ffv.flex_value_set_id
    and ffvh.parent_flex_value = ffv.flex_value
    and ffv.flex_value_set_id = fifs.flex_value_set_id
    and fifs.application_id = 101
    and fifs.id_flex_code = 'GL#'
    and fifs.segment_name = 'LOCATION'
    and fl.lookup_type = 'YES_NO'
    and fl.lookup_code = ffv.enabled_flag
    and SUBSTR(ffvh.child_flex_value_low,1,4) = '1000'
    and SUBSTR(ffv.flex_value,1,4) like 'C___'
    and ffv.enabled_flag = 'Y'
    and ffv.end_date_active is null
    and fifs.id_flex_num = 101
    ---------------------------------------------------------


  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    uh no I am not doing free consulting work

  7. #7
    Join Date
    May 2002
    Posts
    2,645
    Why doesn't select min(empno) work for you? What does a min or max function do you for you behind the scenes? Umm, sort? According to your original post, you want the first sorted row. Well, gee, if they're sorted, what do you think the minimum would be? The first row?

  8. #8
    Join Date
    Jul 2001
    Posts
    334
    Thanks Pando,

    Did you check before deliver the solution, I have checked there is a syntax error. Please correct and post it again it will helpful.

    Thanks,

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    may be you are using some oracle 6, 7

    Code:
    lsc@DEV817>r
      1  select * from
      2  (select * from emp order by deptno) a
      3  where rownum = 1
      4*
    
         EMPNO ENAME      JOB              MGR HIREDATE                    SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- -------------------- ---------- ---------- ----------
          7782 CLARK      MANAGER         7839 1981-JUN-09 00:00:00       2450                    10

  10. #10
    Join Date
    Jul 2001
    Posts
    334
    You got it , I am using 7.3.4
    Is there any way I can work around with 7.3.4 as per my problem?

    Thanks

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