asked    Marcus     2018-10-22       sql       111 view        2 Answers

[SOLVED] Sqlite getting the latest record for this table

I can't figure out what I am doing wrong here. I am querying sqlite table the to get the latest record for each College but the result is not completely accurate. Below is the source table:

College  Status           Date
"UTAR   "CERTIFIED" "07/30/2018"
"UTA"   "CERTIFIED" "03/19/2018"
"UTD"   "CERTIFIED" "06/08/2018"
"UTEL"  "CERTIFIED" "03/13/2018"
"UTSA"  "CERTIFIED" "02/06/2018"
"UTT"   "CERTIFIED" "03/14/2018"
"UTPB"  "CERTIFIED" "07/23/2018"
"UTRG"  "CERTIFIED" "04/12/2018"
"UTAR"  "CERTIFIED" "05/31/2017"
"UTA"   "CERTIFIED" "03/02/2017"
"UTD"   "CERTIFIED" "04/13/2017"
"UTEL"  "CERTIFIED" "03/10/2017"
"UTSA"  "CERTIFIED" "02/13/2017"
"UTT"   "CERTIFIED" "03/11/2017"
"UTPB"  "CERTIFIED" "03/29/2017"
"UTRG"  "CERTIFIED" "04/12/2017" 

and this is my query:

SELECT *
FROM Status_Table AS S1
WHERE Date = (
                 SELECT MAX(Date)
                 FROM Status_Table AS S2
                 WHERE S1.College = S2.College
                                              );

and the result is:

College  Status           Date
"UTAR"  "CERTIFIED" "07/30/2018"
"UTA"   "CERTIFIED" "03/19/2018"
"UTD"   "CERTIFIED" "06/08/2018"
"UTEP"  "CERTIFIED" "03/13/2018"
"UTT"   "CERTIFIED" "03/14/2018"
"UTPB"  "CERTIFIED" "07/23/2018"
"UTRG"  "CERTIFIED" "04/12/2018"
"UTSA"  "CERTIFIED" "02/13/2017"

if you notice in the case of UTSA it did not pick up the latest date of 02/06/2018. Any idea what I am doing wrong here?

  2 Answers  

        answered    Kyle     2018-10-22      

The problem should be related the the column type. From the SQLite document about date time,

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

I don't know why there is no warning or error when you created the table, but it seems that the comparison is using string comparison instead of date. The column is changed to TEXT and the date format 'YYYY-MM-DD' is applied in my sql fiddle. Then using date function in the query and everything seems work.

sql fiddle



        answered    Sam     2018-10-22      

You need group by Max date. Try this -

SELECT *
FROM Status_Table
WHERE Date IN (SELECT MAX(Date)
               FROM Status_Table
               GROUP BY College);




Your Answer





 2018-10-22         Troy

TimePicker inside kendoGrid

First of all I'm aware of this thread : datetimePicker Column in $("#grid").kendoGridBut there might be somothinh what I'm doing wrong.I'm getting data from api properly, so It's not an issue here.Here is my model : schema: { model: { fields: { shopID: "shopID", shpWorkingHourID: "shpWorkingHourID", scheduleDay: "scheduleDay", workStartHour: "workStartHour", workEndHour: "workEndHour", } ...
 jquery                     1 answers                     40 view
 2018-10-22         Regan

Jquery validation error message in checkbox validation

This question already has an answer here: Placing error message for a checkbox array 2 answers jQuery Validation plugin error message placement for radio button 2 answers I found a way to work around it. My checkbox name is 'single_service'. firstly i hide the label error message that created by validation plugin with simple css.label#single_service-error {display: none !important; }then i added invalidHandler...
 jquery                     1 answers                     44 view
 2018-10-22         Hiram

Add fields based on number (Jquery)

I am trying to add X fields according to the number type in the number field or so when the value increases.My template:<div class="form-group row"> <label for="inputEmail3" class="col-sm-10 col-form-label">{l s='Nombre de voyageurs' mod='blocksouhaits'}</label> <div class="col-sm-2"> <input type="number" class="form-control" id="nbVoyager"> </div></div><div class="form-group row"> <div class="col-sm-6"> <input type="text" class="form-control" id="lastnameVoyager" placeholder="Nom"> </div> <div...
 jquery                     1 answers                     46 view