Results 1 to 6 of 6

Thread: Selecting Single from Table (Oracle)

  1. #1
    Join Date
    Nov 2006
    Posts
    3

    Selecting Single Row from Table (Oracle)

    I have a table with 8 columns and multiple rows. One of the columns is a reporting date. I would like to select the row which has the lowest reporting date. I have tried using the min function in a select sub-query but that can return multiple columns . ANy help would be greatly appreciated.
    Last edited by exact; 11-27-2006 at 02:10 PM.

  2. #2
    Join Date
    Oct 2005
    Posts
    2,557
    Returns multiple columns if that is what you are selecting to begin with.

    Select x, y, z from some_table
    where date_column in (select min(date_column)
    from some_other_table);

  3. #3
    Join Date
    Nov 2006
    Posts
    3
    Hi stecal,

    Thanks for taking the time to repspond to my question. Sorry that I was not clear in my original note but I need to return only one row on data. I belive that your expample will return multiples as you noted. I discoverd another thread dated 4-14-2004 with a title of Limiting Results of MIN. I will try that one.

    Thanks

  4. #4
    Join Date
    Nov 2006
    Posts
    3
    Hi stecal,

    Here is the sql which I found:

    select m.participant_id, m.person_met, m.day_met, m.location_number
    from m1 m
    where (m.participant_id, m.person_met, m.day_met) in
    (select x.participant_id, x.person_met, MIN(x.day_met)
    from m1 x
    group by x.participant_id, x.person_met)

  5. #5
    Join Date
    Oct 2005
    Posts
    2,557
    Select x, y, z from some_table
    where date_column in (select min(date_column)
    from some_other_table)
    where rownum = 1;

  6. #6
    Join Date
    Dec 2006
    Posts
    1

    Solution

    Hi,

    Try this query, which will give you desire output.

    select inl.empno, inl.ename, inl.hiredate from (
    (select e.empno, e.ename, min(e.hiredate) hiredate from emp e
    group by e.empno, e.ename) inl
    );

    Regards,
    Ramesh Ramaswamy

Posting Permissions

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