Improve company productivity with a Business Account.Sign Up

x
?
Solved

find lowest date from data

Posted on 2004-10-04
8
Medium Priority
?
820 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 120 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.

Join & Write 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

607 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