CODEWITHSUNDEEP
phpmysql
EnexoOnoma
EnexoOnoma

Reputation: 8834

How to show the last row of a query instead of all the rows happened in a duration?

I use the following query to find who are active in my website (10 seconds) from table a and to display the last page (currently in) from table b, I put a 5 inutes duration. The problem is that in some cases, if browsing is faster than 5 minutes, it will display more than one page.

What I want is to show the output on a per user basis but also to show only the last opened page.

How to do this?

SELECT a.code, b.code, b.page
FROM a
INNER JOIN b ON a.code= b.code
WHERE a.last_active >= NOW( ) - INTERVAL 10 
SECOND AND b.timestamp >= NOW( ) - INTERVAL 5 MINUTE 
ORDER BY b.timestamp DESC

Upvotes: 0

Views: 51

Answers (2)

Vicky21
Vicky21

Reputation: 105

The query below will return the last page for each active entry in the last 10 seconds

SELECT * FROM
(
    SELECT a.code, (SELECT b.page from b where a.code= b.code and b.timestamp >= NOW( ) - INTERVAL 5 MINUTE ORDER BY b.timestamp DESC limit 1) lastPage 
    FROM a
    WHERE a.last_active >= NOW( ) - INTERVAL 10 SECOND
) as sub where sub.lastPage is not null

Upvotes: 1

Yasen Zhelev
Yasen Zhelev

Reputation: 4045

Just join the a table only with the corresponding last result in the b table. There is no point get a.code and b.code in your main SELECT part as they should be the same. For every record in table a that was added within last 10 second you get the related most recent record from table b withing the last 5 minutes.

SELECT a.code,
(
    SELECT page FROM b
    WHERE b.code = a.code
        AND b.timestamp >= NOW( ) - INTERVAL 5 MINUTE
    ORDER BY b.timestamp DESC
    LIMIT 1
)
FROM a
WHERE a.last_active >= NOW( ) - INTERVAL 10

Upvotes: 1

Related Questions