Home » SQL & PL/SQL » SQL & PL/SQL » sql query
sql query [message #257486] Wed, 08 August 2007 08:19 Go to next message
mudabbir
Messages: 235
Registered: April 2006
Location: Kuwait
Senior Member

Assume the following scenario....

Table a1
field1 number(2)
field2 varchar2(10)
field1 is primary key

Table b1
field1 number(2)
field3 date
field1 is foreign key

I have 3 rows in table a1

1 John
2 Jack
3 Mike

I have the following rows in table b1

1 02-AUG-07
1 03-AUG-07
1 07-AUG-07
1 08-AUG-07

2 05-AUG-07
2 08-AUG-07

3 03-AUG-07
3 08-AUG-07
3 09-AUG-07

Assume that today is '08-AUG-07'
I need to pick only those records which shows todays date only
in this example the output should be only

1 08-AUG-07
2 08-AUG-07
Re: sql query [message #257487 is a reply to message #257486] Wed, 08 August 2007 08:21 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
And what have you tried so far?
Re: sql query [message #257488 is a reply to message #257486] Wed, 08 August 2007 08:21 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Please have a look at the documentation for the WHERE clause.

And why not:
3 08-AUG-07
Re: sql query [message #257489 is a reply to message #257486] Wed, 08 August 2007 08:23 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Show us the query you have written and the results from it. We will be glad to help you once you have tried to help yourself.

[TYPO]

[Updated on: Wed, 08 August 2007 08:23]

Report message to a moderator

Re: sql query [message #257494 is a reply to message #257486] Wed, 08 August 2007 08:33 Go to previous messageGo to next message
mudabbir
Messages: 235
Registered: April 2006
Location: Kuwait
Senior Member

The main purpose is finding the maximum date for each group of records in the detail table and then filtering only those records where the max date is '08-AUG-07'

i have tried doing this...

Select field1,max(field3) from b1 where 
to_char(field3,'DD-MM-YYYY')= to_char(sysdate,'DD-MM-YYYY') 
group by internalno;
Re: sql query [message #257503 is a reply to message #257494] Wed, 08 August 2007 08:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Select field1, max(field3) 
from b1 
group by field1
having max(trunc(field3)) = trunc(sysdate) 
/

Regards
Michel
Re: sql query [message #257508 is a reply to message #257486] Wed, 08 August 2007 08:59 Go to previous message
mudabbir
Messages: 235
Registered: April 2006
Location: Kuwait
Senior Member

Thanks Michel,

I think that solves the problem

Mudabbir
Previous Topic: Mutating Error
Next Topic: Send mail from Gmail (merged by LF)
Goto Forum:
  


Current Time: Thu Dec 08 23:53:54 CST 2016

Total time taken to generate the page: 0.12213 seconds