GeeCON Prague 2014*
The moose likes JDBC and the fly likes Trying to grab the last record. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "Trying to grab the last record." Watch "Trying to grab the last record." New topic
Author

Trying to grab the last record.

Justin Char
Ranch Hand

Joined: Mar 05, 2007
Posts: 76
I can grab all sql and put them in desc order.
Then I try to run the code through until it gets to the last Admin key in the series and hold that record and write to transaction file.
Except it gives me all the records not just the 07D one. It gives me all records in the series not just the last one. I need some serious help.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18570
    
    8

Except it gives me all the records not just the 07D one.
Then why not just add another criterion to your WHERE clause?
Justin Char
Ranch Hand

Joined: Mar 05, 2007
Posts: 76
If I put in the where claus to give me 07D's
It will give me all 07D's not the ones that are the max ssn# or oldest date or highest seq # I have been working on this for sometime I have tried many things just not the correct thing.

I want the 07D that has the oldest date and the highest seq # and highest transaction # but at 99 they start over with 1-2-3 again.

I either get all of the last records which is millions
or I get all of the ones I want except I get about one thousand duplicates that have a lower transaction number

example I want the last transaction that has a 07D there are 10 transactions the 4th one is a 07d and the 10th is a 04Z I will get the 07d because there is a 07D in one of the top 10. But I only want it if it is the 10th one. I think I am missing a couple pieces of the puzzle.
Justin
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18570
    
    8

All you are lacking is a coherent description of your requirements.
Originally posted by Justin Charpenter:
I want the 07D that has the oldest date and the highest seq # and highest transaction # but at 99 they start over with 1-2-3 again.
And what if there's an 07D that has the oldest date but not the highest seq #? What if the record with the oldest date and the highest seq # isn't an 07D? And what's that about starting with 1 again? Either it means you can't tell what the highest transaction # is, or it means nothing.
example I want the last transaction that has a 07D there are 10 transactions the 4th one is a 07d and the 10th is a 04Z I will get the 07d because there is a 07D in one of the top 10. But I only want it if it is the 10th one.
Does that mean you want the last record if it's an 07D, or else you want nothing?

Sorry, I'm confused and what you posted didn't help.
[ January 25, 2008: Message edited by: Paul Clapham ]
Justin Char
Ranch Hand

Joined: Mar 05, 2007
Posts: 76
Yes, if the last record is a 07D I want that record.

But I don't want to grab an account that has a 04Q as the (10th)the last record


and because the 4th record with the same adamkey has a 07D if gives me that record.

Only accounts that have an 07D as the last record.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18570
    
    8

Originally posted by Justin Charpenter:
Only accounts that have an 07D as the last record.
So you want to select the last record of each account -- somehow -- and then extract only those that are 07D's? That would be done via a WHERE clause, as I already suggested. So now your problem becomes how to select the last record of each account. This depends on what "last" means. I suspect it has something to do with those dates and seq #'s, right?
Justin Char
Ranch Hand

Joined: Mar 05, 2007
Posts: 76
I can do exactly what I want with one adamkey (one persons account)

I grab the account put it desc order and run it through my java prg and
it grabs the last record.

But when I do it across the whole database I get ones that have an 07D but it is not the last record having an 07D in it.

This is where my problem is. I don't know how to correct this.
Justin Char
Ranch Hand

Joined: Mar 05, 2007
Posts: 76
The only thing I can see working is
creating something that will read through the query line by line
is this adam key transaction # the highest in the set if yes write out.

next adamkey is this the highest trx# no
hold adamkey is this the highest trx# no
hold adamkey is this the highest trx# yes

go to next adamkey

But I just don't know how you would do that.
Justin Char
Ranch Hand

Joined: Mar 05, 2007
Posts: 76
The only thing I can see working is
creating something that will read through the query line by line
is this adam key transaction # the highest in the set if yes write out.

next adamkey is this the highest trx# no
hold adamkey is this the highest trx# no
hold adamkey is this the highest trx# yes

go to next adamkey

But I just don't know how you would do that.
Justin Char
Ranch Hand

Joined: Mar 05, 2007
Posts: 76
You can close this topic....I found my answer.
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 39084
    
  23
. . . Well done. But what was the answer?
 
 
subject: Trying to grab the last record.