0

I'm running a command to retrieve all rows for today with IDs matching 1, 2, 3, etc.

I want to retrieve the latest entry for these IDs for today though.

So rather than getting:

BinID | Timestamp |

2 | 29/04/2021 3:49pm

2 | 29/04/2021 4:41pm

3 | 29/04/2021 2:24pm

I want:

BinID | Timestamp |

2 | 29/04/2021 4:41pm

3 | 29/04/2021 2:24pm

Is this possible?

This is the command I'm running at the moment.

MySqlCommand mysqlcom = new MySqlCommand("SELECT * FROM realtime_gpses WHERE BinId IN (" + binIdsCSVString + ") AND Timestamp >= CURDATE() AND Timestamp < CURDATE() + INTERVAL 1 DAY", mysqlcon);

where binIdsCSVString in this cases is just 2, 3.

juuugh
  • 5
  • 3
  • Use parameterized queries for performance and security. – Tarik Apr 29 '21 at 18:34
  • I am, I'm omitting that part of the code as it's not necessary to solve the issue. – juuugh Apr 29 '21 at 18:35
  • Unless you mean for `Timestamp`, then yes I should do that – juuugh Apr 29 '21 at 18:35
  • You will get three distinct rows with the code you have got right now. – Tarik Apr 29 '21 at 18:35
  • Yes, but I want only 2 rows i.e. the latest for ID 2 and latest for ID 3. – juuugh Apr 29 '21 at 18:36
  • Do you know how I would parameterise `binIdsCSVString `? I tried `SELECT * FROM realtime_gpses WHERE BusId IN (@BusIds)` and ` mysqlcom.Parameters.Add("@BusIds", MySqlDbType.VarChar).Value =busIdsCSVString;` but doesn't seem to like it. – juuugh Apr 29 '21 at 18:43
  • I would rather use a prepared statement and execute one query for each binid if you do not have too many of these. – Tarik Apr 29 '21 at 18:45
  • I see... That's fair... what would be too many? I can't expect in my application there to be more than 10. – juuugh Apr 29 '21 at 18:52

1 Answers1

1

You can try this

Select binid, max(timestamp) from table where ... Group by binid

As an alternative to in (1, 2...) you can generate:

where (binid = @binid1 or binid=@binid2.....)

You then add the parameters accordingly. All of this can be generated dynamically, yet it is parameterized.

See SQL select only rows with max value on a column to get row with max value.

and maybe even better: Retrieving the last record in each group - MySQL

Tarik
  • 10,810
  • 2
  • 26
  • 40
  • Hey Tarik, I just realised, I also have a longitude and latitude column, and the sql command doesn't return the correct longitude and latitude for that row with max(timestamp). How do I need to change the command above in order to get the longitude and latitude entries matching where the max timestamp is? Thanks :) – juuugh Apr 29 '21 at 20:44
  • @juuugh In that case, it _really_ matters which exact version of MySql you're using. Very recent version of MySql can finally use Windowing functions to solve this kind of problem, after years of lagging behind other DBs in this area. But lots of people are still using 5.7, which does not having Windowing functions. – Joel Coehoorn Apr 29 '21 at 21:37
  • Hey Joel, I'm using MySQL 8.0.20 – juuugh Apr 29 '21 at 22:07