0

I have searched on SO prior to asking and have tried things I found - I have more involved due to multiple joins and conditions and cannot get the correct results.

SQL Fiddle here with basic data entered.

The query below does not give the results I want, but gives an idea of what I am looking to achieve. I want to return 1 result per computer_id where time.capture_timestamp is between a specific start/end value and is the highest value in the table for that computer_id including that row's other column values. I have tried a few different things I found here on SO involving MAX() and subqueries, but can't seem to get what I am looking for.

SELECT
  computers.computer_name,
  users.username,
  time.title,
  time.capture_timestamp
FROM computers
INNER JOIN time
  ON time.computer_id = computers.computer_id AND time.capture_timestamp >= 0 AND time.capture_timestamp <= 9999999999
INNER JOIN users
  ON users.user_id = time.user_id
GROUP BY computers.computer_id
ORDER BY time.capture_timestamp DESC

The fiddle as is will return :

computer_name   username    title           capture_timestamp
computer1       user1       some title      1595524341
computer2       user3       some title3     1595524331

while the result I would like is actually :

computer_name   username    title           capture_timestamp
computer1       user2       some title2     1595524351
computer2       user3       some title3     1595524331

... based on the example values in the fiddle. Yes, the start/end time values include 'everything' in this example, but in use there would actually be a timestamp range provided.

user756659
  • 3,372
  • 13
  • 55
  • 110
  • `set sql_mode=ONLY_FULL_GROUP_BY` will help highilght these errors. Both user1 and user1 have a join mapping to computer_id 1. Selection of the maximum here requires a subquery or window function per duplicate link. `ORDER BY` is applied after the aggregation (which is invalid). – danblack Aug 22 '20 at 09:40
  • There is nothing on your question that would demonstrate you have searched this on SO and tried various solutions. Pls make an attempt at using any of the solutions described in the duplicate question. If you cannot make any of those work, then come back with a question describing your attempt based on a solution from the duplicate question, not based on a random hunch! – Shadow Aug 22 '20 at 11:44

0 Answers0