Android Question SQLite Aggregate SubQueries Inside Queries

[Mahares]

Just when I thought I have a good handle on SQLite, I do not. My goal is to display the most recent record of every PROD_WELL.
1. I create a table:

SQL1.ExecNonQuery("CREATE TABLE t1 (PROD_WELL TEXT ,  PROD_DATE TEXT, PROD_TIME TEXT, PROD_CSGP)")

2. I insert a number of records:

Dim MyQuery As String ="INSERT INTO t1  VALUES (?,?,?,?)"     
        SQL1.ExecNonQuery2(MyQuery, Array As String( "6743","20170113", "12:35", "145")) 
        SQL1.ExecNonQuery2(MyQuery, Array As String( "6900","20170317", "10:45", "109")) 
        SQL1.ExecNonQuery2(MyQuery, Array As String( "7345","20170610", "09:15", "539")) 
        SQL1.ExecNonQuery2(MyQuery, Array As String( "6743","20170113", "09:15", "203"))
        SQL1.ExecNonQuery2(MyQuery, Array As String( "8300","20170506", "02:10", "491")) 
        SQL1.ExecNonQuery2(MyQuery, Array As String( "6743","20170112", "13:35", "331"))
        SQL1.ExecNonQuery2(MyQuery, Array As String( "6900","20170317", "06:55", "109"))
        SQL1.ExecNonQuery2(MyQuery, Array As String( "8300","20170501", "02:47", "300"))
        SQL1.ExecNonQuery2(MyQuery, Array As String( "8300","20170506", "02:34", "400"))

3. I run the below query:

MyQuery=$"SELECT * FROM t1 WHERE
    PROD_DATE IN (SELECT Max(PROD_DATE) FROM t1 GROUP BY PROD_WELL )
    AND 
    PROD_TIME IN (SELECT Max(PROD_TIME) FROM t1 GROUP BY PROD_WELL, PROD_DATE ) 
    ORDER BY PROD_DATE, PROD_TIME DESC"$

The data needs to be sorted by PROD_DATE DESC, but if there are records with same PROD_DATE only the the record with the most recent date and most recent PROD_TIME needs displayed. In other words, ONLY one record per PROD_WELL needs to be. displayed.
I do not want to use any ticks, just the data as is
The query yields below but:this line should not be there: 6743,20170113,09:15,203
' 6743,20170113,12:35,145
' 6743,20170113,09:15,203
' 6900,20170317,10:45,109
' 8300,20170506,02:34,400
' 7345,20170610,09:15,539

 

[KMatle]

See here: https://stackoverflow.com/questions/189213/sql-selecting-rows-by-most-recent-date

 

[Mahares]

I tried all the below queries possibilities and none yields the correct number of rows and data I am looking for in post #1.
I need to return these 4 rows only:
6743,20170113,12:35,145
6900,20170317,10:45,109
8300,20170506,02:34,400
7345,20170610,09:15,539

MyQuery=$"Select t.PROD_WELL, t.PROD_DATE, t.PROD_TIME, t.PROD_CSGP  FROM(
    Select PROD_WELL,Max(PROD_DATE) As PROD_DATE, Max(PROD_TIME) As PROD_TIME
    FROM t1
    GROUP BY PROD_WELL) x
    JOIN t1 t ON x.PROD_WELL =t.PROD_WELL
    And x.PROD_DATE = t.PROD_DATE
    And x.PROD_TIME = t.PROD_TIME
    "$

MyQuery=$"Select t.PROD_WELL,  t.PROD_DATE, t.PROD_TIME, t.PROD_CSGP  FROM(
    Select PROD_WELL,Max(PROD_DATE) As PROD_DATE
    FROM t1
    GROUP BY PROD_WELL) x
    JOIN t1 t ON x.PROD_WELL =t.PROD_WELL
    And x.PROD_DATE = t.PROD_DATE"$

MyQuery=$"Select t.PROD_WELL, t.PROD_DATE, t.PROD_TIME, t.PROD_CSGP FROM(
    Select PROD_WELL,Max(PROD_DATE) As PROD_DATE
    FROM t1
    GROUP BY PROD_WELL) x
    JOIN t1 t ON x.PROD_WELL =t.PROD_WELL
    And x.PROD_DATE = t.PROD_DATE"$

MyQuery=$"Select a.PROD_WELL, a.PROD_DATE,a.PROD_TIME, a.PROD_CSGP
    from t1 a
    left outer join t1 b
    on a.PROD_WELL=b.PROD_WELL And a.PROD_DATE <b.PROD_DATE
    where b.PROD_WELL Is Null
    order by a.PROD_DATE desc"$

 

[Erel]

Sort it by date and time, descending. Go over the results and compare the date of the current result to the previous one. If it is the same then skip over it.

You can add the results to a list with List.InsertAt(0, ...) to build a list in the correct order.

 

[KMatle]

Sort it by date and time, descending. Go over the results and compare the date of the current result to the previous one. If it is the same then skip over it

 

[mc73]

You may want to try the following, though I haven't tested it and I don't quite like it

select prod_well,max(prod_date||substr(prod_time,1,2)||substr(prod_time,4,2)),prod_csgp from t1 group by prod_well

 

[Mahares]

You may want to try the following, though I haven't tested it and I don't quite like it

Sort it by date and time, descending. Go over the results

This is how I finally got it using Sub-queries inside a query:

MyQuery=$" SELECT t1.PROD_WELL, t1.PROD_DATE, t1.PROD_TIME, t1.PROD_CSGP, t1.PROD_DATE||t1.PROD_TIME AS ProdDT
    FROM t1
    INNER JOIN   (SELECT PROD_WELL, Max(PROD_DATE||PROD_TIME) AS MaxDT
    FROM t1 GROUP BY PROD_WELL) tx ON t1.PROD_WELL=tx.PROD_WELL 
    AND ProdDT=MaxDT
    ORDER BY t1.PROD_WELL, t1.PROD_DATE DESC  "$

6743,20170113,12:35,145
6900,20170317,10:45,109
7345,20170610,09:15,539
8300,20170506,02:34,400