Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# find lowest date from data

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

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the dâ€¦
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platformsâ€¦
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
###### Suggested Courses
Course of the Month6 days, left to enroll