Solved

find lowest date from data

Posted on 2004-10-04
8
803 Views
Last Modified: 2012-05-05
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
Comment
Question by:plennon
8 Comments
 
LVL 3

Accepted Solution

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

Expert Comment

by:KrishnaRoy
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

by:jaramill
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

by:ishando
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 9

Expert Comment

by:pratikroy
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

by:pratikroy
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

by:jaramill
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

by:chpk
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.
Please help me with this,,very urgent..Thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now