• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Trying to grab the last record.

 
Ranch Hand
Posts: 76
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 76
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 76
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 76
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 76
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 76
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 76
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You can close this topic....I found my answer.
 
Marshal
Posts: 79177
377
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
. . . Well done. But what was the answer?
reply
    Bookmark Topic Watch Topic
  • New Topic