x
Solved

# find lowest date from data

Posted on 2004-10-04
Medium Priority
820 Views
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.

0
Question by:plennon

LVL 3

Accepted Solution

jaramill earned 120 total points
ID: 12219003
SELECT MAX(t1.ldate)
FROM t1, t2
WHERE t1.lid = t2.lid
AND t2.lnum = 9;
0

Expert Comment

ID: 12219101
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

LVL 3

Expert Comment

ID: 12219130
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

LVL 15

Expert Comment

ID: 12222609
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

LVL 9

Expert Comment

ID: 12225006
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

LVL 9

Expert Comment

ID: 12225009
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

LVL 3

Expert Comment

ID: 12228483
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

Expert Comment

ID: 32708570
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.
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to recover a database from a user managed backup
###### Suggested Courses
Course of the Month10 days, 13 hours left to enroll