SQL*plus Query -- Very urgent

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

select min(empno) from table_name;

Not helpful, I need the solution according to my problem in original post.


Thanks

select * from
(select * from emp order by deptno) a
where rownum = 1

:D

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

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
---------------------------------------------------------

uh no I am not doing free consulting work :D

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?

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,

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

---

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

oh yes index the column

Oh, I get it - you didn't write want you meant:

"What I need is I want to order by empno and then select the first empno sorted empno. "

Where is deptno in all of this?

I invented deptno :D

Oh.

Select * from emp where empno in
(select min(empno) from emp);

Thanks you both,
I am still on the same page; Index is not possible for some reason. All I am looking if there is way I can resolve this issue. I suppose you have more power, ability, expert level and professional knowledge.

Thanks

I am sorry but your suppose is wrong :D

if you are using 7.3.4 is what you get :D

Honest to God, this works:

Select * from emp where empno in
(select min(empno) from emp);