Results 1 to 2 of 2

Thread: query help

  1. #1
    Join Date
    Oct 2010

    Unanswered: query help

    i have a customer table and env table. i want to select the max date time when the emp have joined with the customer. i want the complete record having max date_time tat table.

    select,e.date_time,c.cust,max(date_time) from env e ,customer c where c.cust='kuoron' and e.cust=c.cust

    modify the query. dont know when i run this query i am getting all the records with max date time. i need only one record from env table having max date_time.

  2. #2
    Join Date
    Jan 2004
    Croatia, Europe
    Provided Answers: 4
    It would be easier if you provided a test case (i.e. CREATE TABLE and INSERT INTO sample data so that we would see what you have, as well as desired output).

    Here's an example based on Scott's schema. First, all employees sorted by departments and date columns:
    SQL> alter session set nls_date_format = '';
    Session altered.
    SQL> break on deptno
    SQL> select deptno, ename, hiredate
      2  from emp
      3  order by deptno, hiredate desc;
    ---------- ---------- ----------
            10 MILLER     23.12.1982
               KING       17.11.1981
               CLARK      09.06.1981
            20 ADAMS      12.01.1983
               SCOTT      09.12.1982
               FORD       03.12.1981
               JONES      02.04.1981
               SMITH      17.12.1980
            30 JAMES      03.12.1981
               MARTIN     28.09.1981
               TURNER     08.09.1981
               BLAKE      01.05.1981
               WARD       22.02.1981
               ALLEN      20.02.1981
    14 rows selected.
    We are interested in records with maximum dates per departments (Miller, Adams and James):
    SQL> select e.deptno, e.ename, e.hiredate
      2  from emp e
      3  where e.hiredate = (select max(e1.hiredate)
      4                      from emp e1
      5                      where e1.deptno = e.deptno
      6                     )
      7  order by e.deptno;
    ---------- ---------- ----------
            10 MILLER     23.12.1982
            20 ADAMS      12.01.1983
            30 JAMES      03.12.1981
    If I understood what you said, then you might try to apply the above code to your situation.

Posting Permissions

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