• 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Devaka Cooray
  • Knute Snortum
  • Paul Clapham
  • Tim Cooke
Sheriffs:
  • Liutauras Vilda
  • Jeanne Boyarsky
  • Bear Bibeault
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Ron McLeod
  • Piet Souris
  • Frits Walraven
Bartenders:
  • Ganesh Patekar
  • Tim Holloway
  • salvin francis

How can I return the last int value with at least one value matches on another column in the table?  RSS feed

 
Ranch Hand
Posts: 194
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi guys, I would like to ask for your help with regards to selecting the necessary records in the DB using MySQL. I have a batch that is executing. Each batch has batchId (integer) and contains many records (no definite count of records per batch). There is a column in the table 'Processed' and it's value is either 'Y' or 'N'. I would like to select the last batchId value that contains at least one 'Y' processed. How can I return batchId = 3 for this example:

given the table:

BatchIdProcessed?
1Y
1Y
1Y
1N
2N
2Y
3N
3N
3N
3N
3N
4N
4N
4N
 
Winston Liek
Ranch Hand
Posts: 194
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sorry, my question is

"How can I return the first batchId with no 'Y' in it?" In my given table, I need to return batchId = 3 since all 5 records under batchId = 3 has 'N' Processed value
 
Saloon Keeper
Posts: 10209
216
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So you need to SELECT the MAX BatchId FROM the Processed table WHERE the Processed column equals 'Y'?
 
Stephan van Hulst
Saloon Keeper
Posts: 10209
216
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Winston Liek wrote:Sorry, my question is [...]


Ah, in that case you need to SELECT the MIN BatchId WHERE the Processed column is not equal to 'Y'.
 
Winston Liek
Ranch Hand
Posts: 194
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Stephan,

I need to select the MIN batchId where it does not contain any 'Y' in it. In other words all 'N' in that batchId
 
Stephan van Hulst
Saloon Keeper
Posts: 10209
216
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Okay, so what's the problem? The query is almost verbatim what I wrote in the last post.
 
Winston Liek
Ranch Hand
Posts: 194
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi, Thank you very much for your follow up response.

Are you referring to this code:



But it does not return 3. It returns 1 instead
 
Winston Liek
Ranch Hand
Posts: 194
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The reason I am asking this is because we have an app that processes the batch. Initially, all records have Processed values = 'N'. When the application runs, it scans BatchId per batchId, row per row. After the method is done for that row, it updates Processed='Y'. However, when it encounters an error, the app stops.

Therefore, I need to create a report which last batchId should I continue to resume my app running.
 
Stephan van Hulst
Saloon Keeper
Posts: 10209
216
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ahh wait yes I made a mistake.

Treat this as pseudo-code. I'm not certain about the syntax.
 
Winston Liek
Ranch Hand
Posts: 194
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you for your suggestion. However I found a different approach for this one. I will now mark this as resolved.

 
Stephan van Hulst
Saloon Keeper
Posts: 10209
216
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you share it with us?
 
Winston Liek
Ranch Hand
Posts: 194
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you for your message, since the processed values can only be 2 values, I created a subquery containing only those 2 values

 
Stephan van Hulst
Saloon Keeper
Posts: 10209
216
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That won't work if your table contains batch IDs for which all rows have processed 'Y'.

I think this is what you want:
 
Paper beats rock. Scissors beats tiny ad.
ScroogeXHTML - small and flexible RTF to HTML converter library
https://coderanch.com/t/710903/ScroogeXHTML-RTF-HTML-XHTML-converter
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!