SELECT MIN(“ + Game.COLUMN_SCORE + ”) Not Returning Lowest Value When Score Is Under 100
Clash Royale CLAN TAG#URR8PPP
SELECT MIN(“ + Game.COLUMN_SCORE + ”) Not Returning Lowest Value When Score Is Under 100
For reason I cannot seem to understand, whenever I enter a score that is less than 100 my query does not seem to find the lowest value in the score column. I have gone through my code a dozen times and I cannot seem to find what I did wrong.
Here is my code below:
Series.java
public class Series
public static final String TABLE_NAME = "Series";
public static final String COLUMN_ID = "_id";
public static final String COLUMN_LEAGUE_ID = "league_id";
public static final String COLUMN_BOWLER_ID = "bowler_id";
public static final String COLUMN_NAME = "name";
public static final String COLUMN_SERIES_AVERAGE = "average";
public static final String COLUMN_SERIES_HIGH = "high";
public static final String COLUMN_SERIES_LOW = "low";
public static final String COLUMN_TIMESTAMP = "timestamp";
private int id;
private String league_id;
private String bowler_id;
private String name;
private String average;
private String high;
private String low;
private String timestamp;
// Create table SQL query
public static final String CREATE_TABLE =
"CREATE TABLE IF NOT EXISTS " + TABLE_NAME + "("
+ COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
+ COLUMN_LEAGUE_ID + " TEXT,"
+ COLUMN_BOWLER_ID + " TEXT,"
+ COLUMN_NAME + " TEXT,"
+ COLUMN_SERIES_AVERAGE + " TEXT,"
+ COLUMN_SERIES_HIGH + " TEXT,"
+ COLUMN_SERIES_LOW + " TEXT,"
+ COLUMN_TIMESTAMP + " DATETIME DEFAULT CURRENT_TIMESTAMP"
+ ")";
public Series()
public Series(int id, String league_id, String bowler_id, String name, String average, String high, String low, String timestamp)
this.id = id;
this.league_id = league_id;
this.bowler_id = bowler_id;
this.name = name;
this.average = average;
this.high = high;
this.low = low;
this.timestamp = timestamp;
public int getId()
return id;
public String getLeagueId() return league_id;
public String getBowlerId() return bowler_id;
public String getName()
return name;
public void setName(String name)
this.name = name;
public String getAverage() return average;
public void setAverage(String average)
this.average = average;
public String getHigh() return high;
public void setHigh(String high)
this.high = high;
public String getLow() return low;
public void setLow(String low)
this.low = low;
public String getTimestamp()
return timestamp;
public void setId(int id)
this.id = id;
public void setLeagueId(String league_id)
this.league_id = league_id;
public void setBowlerId(String bowler_id)
this.bowler_id = bowler_id;
public void setTimestamp(String timestamp)
this.timestamp = timestamp;
Function that returns the lowest value to the Series Table:
//Getting Series Low
public String getSeriesLow(String leagueId, String bowlerId, String seriesId)
SQLiteDatabase database = getWritableDatabase();
Integer seriesLow = 0;
Series series = new Series();
ContentValues valuesSeries = new ContentValues();
Cursor cursorSeries = database.rawQuery("SELECT MIN(" + Game.COLUMN_SCORE + ") FROM " + Game.TABLE_NAME + " WHERE " + Game.COLUMN_LEAGUE_ID + " = '" + leagueId + "'" + " AND " + Game.COLUMN_BOWLER_ID + " = '" + bowlerId + "'" + " AND " + Game.COLUMN_SERIES_ID + " = '" + seriesId + "'", null);
if (cursorSeries.moveToFirst())
do
seriesLow = cursorSeries.getInt(0);
while (cursorSeries.moveToNext());
valuesSeries.put(Series.COLUMN_LEAGUE_ID, leagueId);
valuesSeries.put(Series.COLUMN_BOWLER_ID, bowlerId);
valuesSeries.put(Series.COLUMN_SERIES_LOW, seriesLow);
database.update("Series", valuesSeries, Series.COLUMN_LEAGUE_ID + "='" + leagueId + "'" + " AND " + Series.COLUMN_BOWLER_ID + " = '" + bowlerId + "'" + " AND " + Series.COLUMN_ID + " = '" + seriesId + "'",null);
Log.d("***UPDATING SERIES LOW","Series.COLUMN_SERIES_LOW Updated With = " + seriesLow + "***");
cursorSeries.close();
//Close Database Connection
database.close();
return seriesLow.toString();
Here is were I am using it in the GameActivity:
//Deleting Game From SQWLite & Removing The Item From The List By Its Position
private void deleteGame(int position)
//Deleting Game From The Database
db.deleteGame(gameList.get(position));
//Removing Game From The List
gameList.remove(position);
mAdapter.notifyItemRemoved(position);
seriesAverage = db.getSeriesAverage(savedLeagueId, savedBowlerId, savedSeriesId);
bowlerAverage = db.getBowlerAverage(savedLeagueId, savedBowlerId);
leagueAverage = db.getLeagueAverage(savedLeagueId);
seriesHigh = db.getSeriesHigh(savedLeagueId, savedBowlerId, savedSeriesId);
seriesLow = db.getSeriesLow(savedLeagueId, savedBowlerId, savedSeriesId);
toggleEmptyGames();
//Opens Dialog With Edit - Delete Options
private void showActionsDialog(final int position)
CharSequence colors = new CharSequence"Edit", "Delete";
AlertDialog.Builder builder = new AlertDialog.Builder(this);
builder.setTitle("Choose option");
builder.setItems(colors, new DialogInterface.OnClickListener()
@Override
public void onClick(DialogInterface dialog, int which)
if (which == 0)
showGameDialog(true, gameList.get(position), position);
else
deleteGame(position);
);
builder.show();
//Show Alert Dialog With EditText Options to Enter/Edit A Game
//When shouldUpdate=true, It Automatically Displays Old Game & Changes The Button Text to UPDATE
private void showGameDialog(final boolean shouldUpdate, final Game game, final int position)
LayoutInflater layoutInflaterAndroid = LayoutInflater.from(getApplicationContext());
final View view = View.inflate(this, R.layout.dialog_game, null);
AlertDialog.Builder alertDialogBuilderUserInput = new AlertDialog.Builder(GameActivity.this);
alertDialogBuilderUserInput.setView(view);
leagueId.setText(savedLeagueId);
bowlerId.setText(savedBowlerId);
seriesId.setText(savedSeriesId);
final EditText inputScore = view.findViewById(R.id.etScore);
final EditText inputStrikes = view.findViewById(R.id.etStrikes);
final EditText inputSpares = view.findViewById(R.id.etSpares);
final EditText inputSplits = view.findViewById(R.id.etSplits);
final EditText inputSplitConversions = view.findViewById(R.id.etSplitConversions);
final EditText inputOpenFrames = view.findViewById(R.id.etOpenFrames);
TextView dialogTitle = view.findViewById(R.id.dialog_title);
dialogTitle.setText(!shouldUpdate ? "New Game" : "Edit Game");
if (shouldUpdate && game != null)
leagueId.setText(game.getLeagueId());
bowlerId.setText(game.getBowlerId());
seriesId.setText(game.getSeriesId());
inputScore.setText(game.getScore());
inputStrikes.setText(game.getStrikes());
inputSpares.setText(game.getSpares());
inputSplits.setText(game.getSplits());
inputSplitConversions.setText(game.getSplitConversions());
inputOpenFrames.setText(game.getOpenFrames());
alertDialogBuilderUserInput
.setCancelable(false)
.setPositiveButton(shouldUpdate ? "update" : "save", new DialogInterface.OnClickListener()
public void onClick(DialogInterface dialogBox, int id)
)
.setNegativeButton("cancel",
new DialogInterface.OnClickListener()
public void onClick(DialogInterface dialogBox, int id)
dialogBox.cancel();
);
final AlertDialog alertDialog = alertDialogBuilderUserInput.create();
alertDialog.show();
alertDialog.getButton( AlertDialog.BUTTON_POSITIVE).setOnClickListener( new View.OnClickListener()
@Override
public void onClick(View v)
//Show Toast Message When No Text Is Entered
//Validation Being Done Here
Boolean allValid = true;
if (TextUtils.isEmpty(inputScore.getText().toString()))
inputScore.setText( "0" );
//return;
if (TextUtils.isEmpty(inputStrikes.getText().toString()))
inputStrikes.setText( "0" );
//return;
if (TextUtils.isEmpty(inputSpares.getText().toString()))
inputSpares.setText( "0" );
//return;
if (TextUtils.isEmpty(inputSplits.getText().toString()))
inputSplits.setText( "0" );
//return;
if (TextUtils.isEmpty(inputSplitConversions.getText().toString()))
inputSplitConversions.setText( "0" );
//return;
if (TextUtils.isEmpty(inputOpenFrames.getText().toString()))
inputOpenFrames.setText( "0" );
//return;
final int valueScore = Integer.valueOf(inputScore.getText().toString());
final int valueStrikes = Integer.valueOf(inputStrikes.getText().toString());
final int valueSpares = Integer.valueOf(inputSpares.getText().toString());
final int valueSplits = Integer.valueOf(inputSplits.getText().toString());
final int valueSplitConversions = Integer.valueOf(inputSplitConversions.getText().toString());
final int valueOpenFrame = Integer.valueOf(inputOpenFrames.getText().toString());
if (valueScore > 300)
allValid = false;
inputScore.setError("Maximum value allowed is 300");
if (valueStrikes > 12)
allValid = false;
inputStrikes.setError("Maximum value allowed is 12");
if (valueSpares > 10)
allValid = false;
inputSpares.setError("Maximum value allowed is 10");
if (valueSplits > 10)
allValid = false;
inputSplits.setError("Maximum value allowed is 10");
if (valueSplitConversions > 10)
allValid = false;
inputSplitConversions.setError("Maximum value allowed is 10");
if (valueOpenFrame > 10)
allValid = false;
inputOpenFrames.setError("Maximum value allowed is 10");
if (!allValid)
return;
alertDialog.dismiss();
//Check If User Is Updating Game
//Create New Game
if (shouldUpdate && game != null)
//Update Game By It's Id
updateGame(inputScore.getText().toString(), inputStrikes.getText().toString(), inputSpares.getText().toString(), inputSplits.getText().toString(), inputSplitConversions.getText().toString(), inputOpenFrames.getText().toString(), position);
else
createGame(leagueId.getText().toString(), bowlerId.getText().toString(), seriesId.getText().toString(), inputScore.getText().toString(), inputStrikes.getText().toString(), inputSpares.getText().toString(), inputSplits.getText().toString(), inputSplitConversions.getText().toString(), inputOpenFrames.getText().toString());
seriesAverage = db.getSeriesAverage(savedLeagueId, savedBowlerId, savedSeriesId);
bowlerAverage = db.getBowlerAverage(savedLeagueId, savedBowlerId);
leagueAverage = db.getLeagueAverage(savedLeagueId);
seriesHigh = db.getSeriesHigh(savedLeagueId, savedBowlerId, savedSeriesId);
seriesLow = db.getSeriesLow(savedLeagueId, savedBowlerId, savedSeriesId);
);
alertDialog.getButton( AlertDialog.BUTTON_NEGATIVE).setOnClickListener(new View.OnClickListener()
@Override
public void onClick(View v)
alertDialog.dismiss();
);
Any assistance getting this to work would be greatly appreciated. When you look at the screen shots you can see what I am referring too.
I have also just noticed that if all the values are below 100 it appears correctly, it seems to happen when 1 or more values are over 100 and 1 is under 100.
Is there away to convert the values from the COLUMN_SCORE to Integer before the query so that it is performing the comparison on numeric, not alphanumeric?
– Robert Vogl
Aug 12 at 15:29
I think SELECT MIN (CAST(COLUMN_SCORE AS int))
– Jeremy Kahan
Aug 12 at 15:45
Or (better) rethink your table and make that field an integer.
– Kling Klang
Aug 12 at 15:58
Any particular reason you want to store a numeric score as text inside your database? That is the root of your problem.
– Richard Le Mesurier
Aug 12 at 15:59
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
sounds almost like the comparison/sort is alphabetical, not numeric. Then '199' is < '99', which makes sense since they are defined TEXT, not integer.
– Jeremy Kahan
Aug 12 at 13:59