Fetch row that contains the biggest date



IBM's flagship relational database management system

Fetch row that contains the biggest date

Postby vegafacundodaniel » Mon Mar 19, 2012 3:51 pm

Hello,

Could anyone help me with a query please ?

I have to match 2 tables, something like this :

select *
from table1 A
where A.fieldA in (select B.fieldA
from table2 B
where A.id = B.id)

My problem is when I want to select a row in the Table2. I want to choose of the table2 the row that contains the biggest date and the biggest value of the field B.

Something like this

select *
from table1 A
where A.fieldA in (select B.fieldA
from table2 B
where A.id = B.id
order by B.date and B.FieldB
fetch first 1 row only)

but it doesn't work...

Thanks in advance !
vegafacundodaniel
 
Posts: 61
Joined: Tue Jul 20, 2010 4:27 pm
Has thanked: 1 time
Been thanked: 0 time

Re: QUERY

Postby prino » Mon Mar 19, 2012 4:55 pm

Some (expletive deleted) person wrote:...but it doesn't work...

Then maybe you can be so smart to tell us What it doesn't work.
Robert AH Prins
robert.ah.prins @ the.17+Gb.Google thingy
User avatar
prino
 
Posts: 635
Joined: Wed Mar 11, 2009 12:22 am
Location: Vilnius, Lithuania
Has thanked: 3 times
Been thanked: 28 times

Re: Fetch row that contains the biggest date

Postby vegafacundodaniel » Mon Mar 19, 2012 5:42 pm

When I execute that spufi, I get this error :

SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD ORDER. TOKEN ) UNION
EXCEPT WAS EXPECTED

Are you nervious ????

Thanks !
vegafacundodaniel
 
Posts: 61
Joined: Tue Jul 20, 2010 4:27 pm
Has thanked: 1 time
Been thanked: 0 time

Re: Fetch row that contains the biggest date

Postby enrico-sorichetti » Mon Mar 19, 2012 8:38 pm

Are you nervious ????


nobody is nervous ...

but people who whine about something not working without telling anything more
just deserve that all their bytes lose their parity :geek:
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2994
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 164 times

Re: Fetch row that contains the biggest date

Postby vegafacundodaniel » Mon Mar 19, 2012 8:50 pm

Yes, you are right but it is not the correct way to answer a question like this "you can be so smart.."
vegafacundodaniel
 
Posts: 61
Joined: Tue Jul 20, 2010 4:27 pm
Has thanked: 1 time
Been thanked: 0 time

Re: Fetch row that contains the biggest date

Postby BillyBoyo » Mon Mar 19, 2012 9:15 pm

You've been here long enough to know how it works, if you've been paying attention rather than just coming here when stuck.

You've previously asked, in the Cobol forum, about "3.2 for 300" which was a problem relating to your edit session for a file you had created under ISPF. You were lucky that time.

Since you can't let it drop, you've also been around long enough to use the Code tags.

Have you tried to question the error message, tell it the "correct" way to behave around your code?

Read the error message (is that exactly how it appeared?). Understand it in its fullness. Work out what it is telling you about your code. If you can't get it, come back and tell us what you understand by what you have learned.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Fetch row that contains the biggest date

Postby GuyC » Tue Mar 20, 2012 3:52 pm

"order by B.date and B.FieldB" is a syntax error, and at least you would need to order Descending.
Which version of DB2 are you using ?
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: Fetch row that contains the biggest date

Postby vegafacundodaniel » Thu Mar 22, 2012 3:33 pm

Yes, you are right GuyC. I forgot to use DESC in the ORDER BY.

How could I get the db2 version ? I see "DB2 VERSION: V08NF" when I enter in Platinum.

Best regards
vegafacundodaniel
 
Posts: 61
Joined: Tue Jul 20, 2010 4:27 pm
Has thanked: 1 time
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post