I have a table (sampleTable) of website access dates as year-month-day, timestamps as year-month-day hour-minutes-seconds, user ids and website access ids. I want to select the access ids which correspond to the first access that occurred on the most recent day. So far I have attempted this with
select userID, MAX(dayAccessed) as latestDay from sampleTable group by userID;
which returns the most recent day. I then have used this in a inner join to rejoin this query with the original table which orders the columns correctly. My problem is that I cannot access the earliest access id on the most recent day for both user ids as
SELECT sampleTable.dayTimeAccessed, sampleTable.userID, latest.latestDay FROM (SELECT userID, MAX(dayTimeAccessed) AS latestDay from sampleTable group by userID) AS latest INNER JOIN sampleTable ON sampleTable.userID = latest.userID AND sampleTable.dayTimeAccessed limit 1;
only returns one field with the first userID matched.
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `sampleTable` ADD INDEX `sampletable_idx_userid_dayaccessed` (`userID`,`dayAccessed`);
SELECT sampleTable.userID, MAX(sampleTable.dayAccessed) AS latestDay FROM sampleTable GROUP BY sampleTable.userID ORDER BY NULL