CODEWITHSUNDEEP
mysqlgreatest-n-per-group
user756659
user756659

Reputation: 3512

mysql - join table row based on id with the highest column value in the table - multiple joins and conditions

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.

Upvotes: 0

Views: 66

Answers (2)

M Khalid Junaid
M Khalid Junaid

Reputation: 64486

You can add a correlated sub query to get the desired results

select
  computers.computer_name,
  users.username,
  t.title,
  t.capture_timestamp
from computers
inner join time t
  on t.computer_id = computers.computer_id 
  and t.capture_timestamp >= 0 and t.capture_timestamp <= 9999999999
inner join users
  on users.user_id = t.user_id
where t.capture_timestamp =(
  select max(capture_timestamp)
  from time
  where capture_timestamp >= 0 and capture_timestamp <= 9999999999
  and t.computer_id = computer_id
)
order by t.capture_timestamp desc

DEMO

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522471

Using ROW_NUMBER:

WITH cte AS (
    SELECT c.computer_name, u.username, t.title, t.capture_timestamp,
           ROW_NUMBER() OVER (PARTITION BY c.computer_id
                              ORDER BY t.capture_timestamp DESC) rn
    FROM computers c
    INNER JOIN time t ON t.computer_id = c.computer_id
    INNER JOIN users u ON u.user_id = t.user_id
    WHERE t.capture_timestamp BETWEEN 0 AND 9999999999
)

SELECT computer_name, username, title, capture_timestamp
FROM cte
WHERE rn = 1;

Upvotes: 1

Related Questions