Solved

find lowest date from data

Posted on 2004-10-04
8
815 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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 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.
Suggested Courses

623 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