B4J Question [Solved] MySQL retrieve

[Harris]

I have a table...
It has many rows for columns VEH and Datetime (long).

How can I retrieve the latest UNIQUE VEH for Datetime - for all VEH?
I want a single record (from the many) which represents the last record where VEH (datetime) is greater than all other records in the set.

Select * from currposition where mydate <= datetime.now AND VEH UNIQUE ...

This should return a unique VEH (only one) with the last record entered into the table..

Thanks

 

[EnriqueGonzalez]

something like?

SELECT DISTINCT VEH, lastdate
   FROM TABLE1
         INNER JOIN (
              SELECT VEH, MAX(mydate) as lastDate
                  FROM TABLE1
              WHERE mydate <= datetime.now
              GROUP BY VEH
         ) as tbMax on TABLE1.VEH = tbMax.VEH

 

[PCastagnetti]

You can also try it:

SELECT VEH, lastdate
   FROM TABLE1
WHERE lastdate<= datetime.now
order by lastdate desc limit 1

edit:
limit 1 works in sqlite

in SqlServer:

SELECT top 1 VEH, lastdate
   FROM TABLE1
WHERE lastdate<= datetime.now
order by lastdate desc

 

[Harris]

Thanks guys...
Screwing around all day with this... SQL is a bit of magic + knowledge of what you want in one pass...
This is all done on a MySQL DB.

' Option 1
SQL_Str2  = "Select DISTINCT (currposition.veh), lastDate FROM currposition  INNER JOIN (  Select currposition.veh , MAX(currposition.pk) As lastDate  FROM currposition  GROUP BY currposition.veh ) As tbMax on currposition.veh = tbMax.veh"

' This query returns a result set with exactly what I want (- minus all the other columns needed...)  A total of 5 records.. GREAT!
' DISTINCT (currposition.veh), lastDate will only return these two columns (and no way to get the rest of them)
' This is the LAST datetime for each veh in the table.
' However, since I only have the veh and lastDate,  I will have to run another query when processing EACH row to get the other values needed from the table for each returned record... (which I can do but more server hits per row...)


' Option 2
SQL_Str2  = "Select * FROM currposition  INNER JOIN (  Select currposition.veh , MAX(currposition.pk) As lastDate  FROM currposition  GROUP BY currposition.pk DESC ) As tbMax on currposition.veh = tbMax.veh Group by currposition.veh DESC "

' This return all fields required - (for the 5 records) BUT - This is the FIRST datetime for each veh in the table ( not the last as I would expect / need ).

' currposition.pk is the datetime (long)

Should I bite the bullet and move on with Option 1? (more processing).

Thanks

 

[EnriqueGonzalez]

i would go for the first option,

MySQL is blazing fast when doing queries and subqueries, at work i have some queries with up to 4 levels and they are done in less than a second.

may be a bit of tweaking for query1 IF you have a table alone for Vehicles

please take it as pseudocode, but the idea is there.

            SELECT veh, tbMax.lastDate, Details.*
                FROM TableVehicles
                INNER JOIN (
                    Select currposition.veh
                    , MAX(currposition.pk) As lastDate  
                        FROM currposition  
                        GROUP BY currposition.veh 
                ) As tbMax on TableVehicles.veh = tbMax.veh
               
                INNER JOIN (
                    SELECT * 
                        FROM currposition
                ) as Details on tbMax.veh = Details.veh AND tbMax.lastDate = Details.pk

 

[Harris]

i would go for the first option,

MySQL is blazing fast when doing queries and subqueries, at work i have some queries with up to 4 levels and they are done in less than a second.

may be a bit of tweaking for query1 IF you have a table alone for Vehicles

please take it as pseudocode, but the idea is there.

            SELECT veh, tbMax.lastDate, Details.*
                FROM TableVehicles
                INNER JOIN (
                    Select currposition.veh
                    , MAX(currposition.pk) As lastDate 
                        FROM currposition 
                        GROUP BY currposition.veh
                ) As tbMax on TableVehicles.veh = tbMax.veh
              
                INNER JOIN (
                    SELECT *
                        FROM currposition
                ) as Details on tbMax.veh = Details.veh AND tbMax.lastDate = Details.pk

I shall give it a try... MySQL is generally a joy to work with - IF you know how to construct quires.
I shall have to see if this returns all columns for the last (MAX(datetime)) for the 5 vehicles of interest...

Man what fun...

Thanks

 

[EnriqueGonzalez]

Today exactly i had to do something a like (taking last dates)! ... it was 5 hours writting code, and not the fun B4J code, but the Vb.Net + SQl Server code.

The result was a more than a 100 lines of queries.

 

[OliverA]

SQL_Str2 = "Select * FROM currposition INNER JOIN ( Select currposition.veh , MAX(currposition.pk) As lastDate FROM currposition GROUP BY currposition.pk DESC ) As tbMax on currposition.veh = tbMax.veh Group by currposition.veh DESC "

This is almost right, but seems to contain a misplaced GROUP BY and DESC.

How about:

SELECT *
FROM currposition 
INNER JOIN (
      SELECT currposition.veh, MAX(currposition.pk) AS lastDate 
      FROM currposition
      GROUP BY currposition.pk) AS tbMax
   ON currposition.veh = tbMax.veh AND currposition.pk = tbMax.lastDate
ORDER BY currposition.veh DESC

This is assuming that the sort was to show the vehicles in descending order.

Today exactly i had to do something a like (taking last dates)! ... it was 5 hours writting code, and not the fun B4J code, but the Vb.Net + SQl Server code.

The result was a more than a 100 lines of queries.

If one has to write that much code, then I'm not sure if a relational database was the right storage for the data. Writing lots of code to get your data reminds me of the ISAM days, but relational data should be structured where you can get most of your answer via a handful of SQL statements (may get complex, but should not involve hundred's of queries).

 

[EnriqueGonzalez]

This is almost right, but seems to contain a misplaced GROUP BY and DESC.

How about:

SELECT *
FROM currposition
INNER JOIN (
      SELECT currposition.veh, MAX(currposition.pk) AS lastDate
      FROM currposition
      GROUP BY currposition.pk) AS tbMax
   ON currposition.veh = tbMax.veh AND currposition.pk = tbMax.lastDate
ORDER BY currposition.veh DESC

This is assuming that the sort was to show the vehicles in descending order.



If one has to write that much code, then I'm not sure if a relational database was the right storage for the data. Writing lots of code to get your data reminds me of the ISAM days, but relational data should be structured where you can get most of your answer via a handful of SQL statements (may get complex, but should not involve hundred's of queries).

And you are right! i could have done it in 10 small queries, but when accesing remote databases, its better to call one big query that retrives as many information as possible. One query to capture all the relevant data for a manager report.

 

[OliverA]

manager report

That right there says it all!

 

[Harris]

Got it working... Here is how.

The initial query to return lastdate results for each unique unit number..

Sub SetQuery(fromPage As Int)   

    SQL_str  = "Select Count(DISTINCT currposition.veh, lastDate) as IDS FROM currposition  INNER JOIN (  Select currposition.veh , MAX(currposition.pk) As lastDate  FROM currposition  GROUP BY currposition.veh ) As tbMax on currposition.veh = tbMax.veh  ORDER BY lastDate DESC, currposition.veh "
    ' get a count - used for pagination
    SQL_Str2  = "Select DISTINCT (currposition.veh), lastDate FROM currposition  INNER JOIN (  Select currposition.veh , MAX(currposition.pk) As lastDate  FROM currposition  GROUP BY currposition.veh ) As tbMax on currposition.veh = tbMax.veh  ORDER BY lastDate DESC, currposition.veh "
    SQL_Str2 = SQL_Str2&"  LIMIT "& ((fromPage - 1) * iRecs) & ", "&iRecs
    ' get unique records according to which pagination page

End Sub

This is very fast indeed in MySQL.
Then currposition table will get updated about every 20 minutes with a new location from each (working)vehicle in the fleet.
I shall only keep about one weeks worth (or less) of data in this table - to keep size down.
I shall use a timer to refresh the view (and clean table when day changes) which shows the most recent vehicle reporting in.

Thanks for all the pointers...

   Dim SQL As SQL = DBM.GetSQL

    SetQuery(fromPage)

    Dim numcases As Int = DBM.SQLSelectSingleResult(SQL, SQL_str)  ' get total number of recods
    Dim cases1 As List = DBM.SQLSelect(SQL, SQL_Str2,Null)   ' get unique record list

    Dim cases As List  ' create a new list that we shall use to show records
    cases.Initialize
   
    zonemap.Clear
   
    tblCases.Clear
    datemap.Initialize
    datemap.Clear

    For i = 0 To cases1.Size - 1                      ' unique records with 2 columns (veh, lastdate)
        Dim recs As Map = cases1.Get(i)
    '    Log("Tblfields Map add to date map:  "&recs)
        Dim veh As String = recs.Get("veh")
        Dim pk As String = recs.Get("lastdate")
        datemap.Put(veh,pk)

        Dim SQL_3 As String = "Select * FROM currposition where veh = ? AND pk = ? "   ' query to get ALL columns
     '   Log(" sql3 str: "&SQL_3)
        Dim cl As List = DBM.SQLSelect(SQL, SQL_3,Array As String(veh,pk ))  ' ABMaterial function...
        Dim recs1 As Map = cl.Get(0)   ' convert returned list to a map...
        cases.Add( recs1) ' add map to new list
    Next
    
DBM.SQLClose(SQL)


    For i = 0 To cases.Size - 1
        Dim tblFields As Map = cases.Get(i) 
        ' process records using all required columns...
    Next

 

[EnriqueGonzalez]

Seems awesome!

just one small suggestion, levarage the B4X Power!

dim sql_str as string = $"
"Select Count(DISTINCT currposition.veh, lastDate) as IDS
            FROM currposition
            INNER JOIN (
                      Select currposition.veh , MAX(currposition.pk)
                      As lastDate
            FROM currposition
            GROUP BY currposition.veh
            ) As tbMax on currposition.veh = tbMax.veh
ORDER BY lastDate DESC, currposition.veh "
"$
'way easier to read.

 

[Harris]

Seems awesome!

just one small suggestion, levarage the B4X Power!

dim sql_str as string = $"
"Select Count(DISTINCT currposition.veh, lastDate) as IDS
            FROM currposition
            INNER JOIN (
                      Select currposition.veh , MAX(currposition.pk)
                      As lastDate
            FROM currposition
            GROUP BY currposition.veh
            ) As tbMax on currposition.veh = tbMax.veh
ORDER BY lastDate DESC, currposition.veh "
"$
'way easier to read.

Yes, I keep forgetting... Will clean it up so I can read it..

Thanks

 

[OliverA]

SQL_Str2 = "Select DISTINCT (currposition.veh), lastDate FROM currposition INNER JOIN ( Select currposition.veh , MAX(currposition.pk) As lastDate FROM currposition GROUP BY currposition.veh ) As tbMax on currposition.veh = tbMax.veh ORDER BY lastDate DESC, currposition.veh "

@Harris , if all you're looking for is the vehicle # and the max date from currposition, then this SQL statement can be pretty much taken down to

SELECT currposition.veh , MAX(currposition.pk) As lastDate 
FROM currposition
GROUP BY currposition.veh
ORDER BY lastDate DESC, currposition.veh

The inner join is not necessary. It was only necessary since before it looked like you needed other fields in the [currposition] database that had nothing to do with the grouping. The DISTINCT keyword in this case is not necessary since the GROUP BY will produce only one record per vehicle id.

 

[Harris]

@Harris , if all you're looking for is the vehicle # and the max date from currposition, then this SQL statement can be pretty much taken down to

SELECT currposition.veh , MAX(currposition.pk) As lastDate
FROM currposition
GROUP BY currposition.veh
ORDER BY lastDate DESC, currposition.veh

The inner join is not necessary. It was only necessary since before it looked like you needed other fields in the [currposition] database that had nothing to do with the grouping. The DISTINCT keyword in this case is not necessary since the GROUP BY will produce only one record per vehicle id.

That seems like it may work.
I was try something like that before and it seemed to give me the max date - but all other column data was from the FIRST record (and not the last).
Will try after lunch... and let you know.

 

[OliverA]

SQL_str = "Select Count(DISTINCT currposition.veh, lastDate) as IDS FROM currposition INNER JOIN ( Select currposition.veh , MAX(currposition.pk) As lastDate FROM currposition GROUP BY currposition.veh ) As tbMax on currposition.veh = tbMax.veh ORDER BY lastDate DESC, currposition.veh "

This needs no inner join and you don't have to worry about ordering your sub-query, nor aliasing your MAX() since you are just counting records (regardless of order). MySQL does require aliasing the sub-query, so that stands as is. I took out the "As IDS" too, since this query seems to only be used to retrieve a single result.

SELECT COUNT(*)
FROM (SELECT currposition.veh , MAX(currposition.pk)
   FROM currposition
   GROUP BY currposition.veh) as tbMax

 

[Harris]

This works... The lastdate is the MAX last pk. No extra records - only each unique veh.
Count code works as well.

SQL_Str2 = $"Select  currposition.veh , Max(currposition.pk) As lastDate
            FROM currposition
            GROUP BY currposition.veh
            ORDER BY lastDate DESC, currposition.veh"$

However... if I (Select *, currposition.veh , Max(currposition.pk) As lastDate)...
then I get all columns but the record is the FIRST row found - and lastdate IS the max pk (correctly)...
Freakin strange!
I wanted all data in one query so I didn't need to back and get all columns a second time

SQL_Str2 = $"Select  *, currposition.veh , Max(currposition.pk) As lastDate
            FROM currposition
            GROUP BY currposition.veh
            ORDER BY lastDate DESC, currposition.veh"$

 

[OliverA]

SQL_Str2 = $"Select *, currposition.veh , Max(currposition.pk) As lastDate
FROM currposition
GROUP BY currposition.veh
ORDER BY lastDate DESC, currposition.veh"$

GROUP BY will produce strange results for fields that are not aggregated. You've got currposition.veh covered with the GROUP BY and currpostion.pk with the MAX. Any other field may be any of the values that are assigned to any records that have the same currposition.veh value. Therefore lastdate and currposition.pk may not match at all. If you use:

SELECT *
FROM currposition
INNER JOIN (
      SELECT currposition.veh, MAX(currposition.pk) AS lastDate
      FROM currposition
      GROUP BY currposition.pk) AS tbMax
   ON currposition.veh = tbMax.veh AND currposition.pk = tbMax.lastDate
ORDER BY lastDate DESC, currposition.veh

Then currposition.pk and lastdate in each record should match. One of the key items here (that you did not have in your original SQL statement) is the "AND currposition.pk = tbMax.lastDate" as part of the ON clause. Without that extra statement fragment, the JOIN produced multiple vehicle id's instead of a single one. You then tried to compensate for that with the DISTINCT clause, which can work, but only hides the underlying issue. I hope this makes sense...

 

[Harris]

By Joe, I think you've got it!!! (with one small change..)

GROUP BY currposition.pk) AS tbMax - should be
GROUP BY currposition.veh) AS tbMax


These records match the last veh / pk in the table! Sweet - all in a single pass (query)!

Finished loading and connecting page
Each Tblfields Map: (MyMap) {id=163, pk=1495727604483, drvid=1042, veh=55555, onshift=1, work=0, lat=50.7316282, lon=-120.0393811, currspeed=0.0, comp_id=70, sent=0, lastdate=1495727604483}
Each Tblfields Map: (MyMap) {id=161, pk=1495725501474, drvid=1042, veh=55777, onshift=1, work=0, lat=50.7299352, lon=-120.0256379, currspeed=21.8, comp_id=70, sent=0, lastdate=1495725501474}
Each Tblfields Map: (MyMap) {id=147, pk=1495701455456, drvid=1042, veh=552333, onshift=1, work=1, lat=50.7316754, lon=-120.0393516, currspeed=0.0, comp_id=70, sent=0, lastdate=1495701455456}
Each Tblfields Map: (MyMap) {id=128, pk=1495671694362, drvid=1042, veh=132777, onshift=0, work=0, lat=50.7344583, lon=-120.2308422, currspeed=50.3, comp_id=70, sent=0, lastdate=1495671694362}
Each Tblfields Map: (MyMap) {id=125, pk=1495670490459, drvid=1042, veh=93333, onshift=1, work=1, lat=50.7122575, lon=-120.3107522, currspeed=52.0, comp_id=70, sent=0, lastdate=1495670490459}
Each Tblfields Map: (MyMap) {id=75, pk=1495589510272, drvid=1042, veh=210244, onshift=1, work=1, lat=50.7316842, lon=-120.0393326, currspeed=0.0, comp_id=70, sent=0, lastdate=1495589510272}
Each Tblfields Map: (MyMap) {id=71, pk=1495586805308, drvid=1042, veh=21045, onshift=1, work=1, lat=50.7557273, lon=-120.1570492, currspeed=54.8, comp_id=70, sent=0, lastdate=1495586805308}
Each Tblfields Map: (MyMap) {id=65, pk=1495584702281, drvid=1042, veh=21044, onshift=1, work=1, lat=50.7523934, lon=-120.1080273, currspeed=49.2, comp_id=70, sent=0, lastdate=1495584702281}
Each Tblfields Map: (MyMap) {id=58, pk=1495575387368, drvid=1042, veh=16734, onshift=1, work=1, lat=50.7294017, lon=-120.0247434, currspeed=11.7, comp_id=70, sent=0, lastdate=1495575387368}
locationPage84d8f5fa-dc19-4a25-878d-d59d7a2cc5ea
event name: gm1_ready (MyMap) {eventparams=, eventname=gm1_ready}
event name: gm1_ready (MyMap) {eventparams=, eventname=gm1_ready}

Nice to have SQL experts in the camp!
Thanks SO Much!


I color coded the icons based on Loaded (green) or empty (red) - work=1 means loaded...

 

[OliverA]

GROUP BY currposition.pk) AS tbMax - should be
GROUP BY currposition.veh) AS tbMax

I'm glad you caught that! That would be another subtle bug. That's what I get for going off the top of my head and using copy paste (thus propagating the error on and on). As to myself, no SQL expert here. I've just done enough headbanging with SQL that I know some of the pitfalls (but only some).