find lowest date from data

i have 2 tables

table one

name    id     date  
A1B      123      02/10/04
A1B      456      03/10/04
A1B      678      04/10/04    
A1B      999      04/10/04

table two

id         num
123         7
456         9
678         9
999         8

the sql query takes 'num' as an input from the user

here's what i've done so far -

i find the 'id' where

the 'num' in table two = 9

'id' will = 456 & 678.

from these 'id' i want to find(from table one) which one has the oldest date.














plennonAsked:
Who is Participating?
 
jaramillConnect With a Mentor Commented:
SELECT MAX(t1.ldate)
  FROM t1, t2
 WHERE t1.lid = t2.lid
   AND t2.lnum = 9;
0
 
KrishnaRoyCommented:
SELECT ID from table one
where ID in (456,678)
and ROWNUM <=1
and date is NOT NULL
ORDER BY date;
I used date is NOT NULL because NULL is undefined in Oracle.
Thanks
Krishna
0
 
jaramillCommented:
Oops...didn't see that you wanted the ID.  In this case just do this

SELECT t1.lid, MAX(t1.ldate)
  FROM t1, t2
 WHERE t1.lid = t2.lid
   AND t2.lnum = 9
 GROUP BY t1.lid;
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
ishandoCommented:
SELECT lid, ldate
FROM
  (select t1.lid, t1.date
   FROM t1, t2
   WHERE t1.lid = t2.lid
   AND t2.lnum = 9
   order by date nulls last)
where rownum = 1;
0
 
pratikroyCommented:
If your table one is :
TABLE1
---------
NAME
ID
DATECOL

and table two is:
TABLE2
---------
ID
NUM

Then try :

Select Min(A.datecol) From
table1 A, table2 B
Where A.ID = B.ID
And B.ID = 9;

Hope this will help!
0
 
pratikroyCommented:
oops ... you have to check num=9 ;)

Select Min(A.datecol) From
table1 A, table2 B
Where A.ID = B.ID
And B.NUM = 9;
0
 
jaramillCommented:
Correction on my solution.....I too didn't realize ... you said "oldest" date so like pratikroy, you have to use MIN not MAX.

Gio
0
 
chpkCommented:
I have the similar kind of problem,
I use the below code to get MIN date

SELECT  MIN (b.t_date), b.t_id
FROM ps_t  b, ps_m_t a
WHERE  a.elid = b.eid
      AND a.e_nx = '27307'
      GROUP BY  b.t_id

then i got output as
 MIN (b.t_date)           b.t_id
    8/10/1986               1ELG
   10/22/1984            1POW

But i need the row with Minimum date  only in two of them.
Please help me with this,,very urgent..Thanks
0
All Courses

From novice to tech pro — start learning today.