Solved

find lowest date from data

Posted on 2004-10-04
8
814 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
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 how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

734 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