Beginner SQL Query Question

Ignition
1

I’m playing around with reporting and I would like to get the record for each date between dates.

I have the StartDate and EndDate on calendars and I can retrieve data between the two selected dates but I don’t know how the grab only the last record for each date.

Below is the query I have so far.

SELECT transaction.t_stamp,

transaction.AveAmps,

transaction.Random

FROM transaction

WHERE transaction.t_stamp >= ? AND transaction.t_stamp <= ?

ORDER BY transaction.t_stamp

I’m testing at home on 8.0.10 but have an older version at work ~7.something

Any pointers appreciated.

Thanks,

2

It depends on the type of database you are using: MySQL, MS SQL, etc…

In MS SQL to get the last record use:

SELECT TOP 1 t_stamp, AveAmps, Random
FROM transaction
WHERE t_stamp >= ? AND t_stamp <= ?
ORDER BY t_stamp desc

3

I'm using MySQL sorry for not stating that.

4

Looks like the Select TOP 1 in My SQL is the same as LIMIT 1. I’ve tried this and get only the last record between the dates.

So if I had a range of 10 dates I would only retrieve one record not 10.

Hope that makes sense.

5

Sounds like you’re also looking for the GROUP BY statement. Try GROUP BY t_stamp before your ORDER BY statement

6

Ok, that’s different to what I orginally thought you were after.
As jonathan suggests, maybe something like this:

SELECT
T.t_stamp,
T.AveAmps,
T.Random
FROM transaction T
INNER JOIN
(
SELECT MAX(t_stamp) max_time
FROM transaction
GROUP BY date(t_stamp)
) AS D
ON T.t_stamp = D.max_time

7

In t-SQL (Microsoft SQL Server), you can use:

SELECT *
FROM test
WHERE t_stamp IN
(SELECT MAX(t_stamp)
FROM test
GROUP BY FORMAT(t_stamp, 'yyyy-MM-dd'))

Or, untested:

SELECT transaction.t_stamp,
transaction.AveAmps,
transaction.Random
FROM transaction
WHERE t_stamp IN
(SELECT MAX(t_stamp)
 FROM transaction
 WHERE t_stamp >= ? AND t_stamp <= ?
 GROUP BY FORMAT(t_stamp, 'yyyy-MM-dd')
)
ORDER BY transaction.t_stamp

If SQLines - Online SQL Conversion - SQL Scripts, DDL, Queries, Views, Stored Procedures, Triggers, Embedded SQL is anything to go by, apparently this will work as-is in MySQL as well (the only thing I expected to change would have been the ‘FORMAT’ part)

@DavidWisely I wonder if your method using the inner join is better for performance? :thinking:

8

David’s code gets me what I was originally looking for. Now that I’m at work, going to try and add more improvements.
Thanks All and I’m sure I’ll return.