OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » sql query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sql query [message #257486]

Wed, 08 August 2007 08:19

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

Report message to a moderator

Re: sql query [message #257487 is a reply to message #257486]

Wed, 08 August 2007 08:21

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

And what have you tried so far?

Report message to a moderator

Re: sql query [message #257488 is a reply to message #257486]

Wed, 08 August 2007 08:21

joy_division
Messages: 4963
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

Report message to a moderator

Re: sql query [message #257489 is a reply to message #257486]

Wed, 08 August 2007 08:23

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

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;

Report message to a moderator

Re: sql query [message #257503 is a reply to message #257494]

Wed, 08 August 2007 08:48

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Select field1, max(field3) 
from b1 
group by field1
having max(trunc(field3)) = trunc(sysdate) 
/

Regards
Michel

Report message to a moderator

Re: sql query [message #257508 is a reply to message #257486]

Wed, 08 August 2007 08:59

mudabbir
Messages: 235
Registered: April 2006
Location: Kuwait

Senior Member

Thanks Michel,

I think that solves the problem

Mudabbir

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Dec 07 00:21:46 CST 2024