CODEWITHSUNDEEP
mysqlselectjoininner-join
hjpotter92
hjpotter92

Reputation: 80639

Query to fetch most recent and highest activity of user

My table structure is as follows:

CREATE TABLE `scores` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `nick` VARCHAR(32) NOT NULL,
    `count` SMALLINT(5) UNSIGNED ZEROFILL NOT NULL DEFAULT '00000',
    `messages` SMALLINT(5) UNSIGNED ZEROFILL NOT NULL DEFAULT '00000',
    `dated` DATE NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `nick_dated` (`nick`, `dated`),
    INDEX `nick` (`nick`),
    INDEX `count` (`count`),
    INDEX `messages` (`messages`)
)
COMMENT='Chat scores'
COLLATE='utf8_general_ci'
ENGINE=MyISAM;

I wish to write a query so that for a user input value of nick, I can generate the user's:

The query I've written is:

SELECT s.`nick` AS `nick`,
    s.`count` AS `recent`,
    t.`total` AS `total`,
    t.`avrg` AS `avrg`,
    MAX(ss.`count`) AS `max`,
    ss.dated
FROM (
    SELECT `nick`,
        SUM(`count`) AS `total`,
        AVG(`count`) AS `avrg`,
        MAX(`dated`) AS `dated` # For most recent activity
    FROM `scores`
    WHERE `nick` = 'hjpotter92'
) AS `t`
INNER JOIN scores s
    ON s.nick = t.nick
        AND s.dated = t.dated
INNER JOIN scores ss
    ON ss.nick = t.nick

I'm able to select the first three required values. But how would I be able to get the date of highest activity. Here's a sqlfiddle. As you can see in the fiddle DDL, line #24

INSERT INTO `scores` 
    (`count`, `nick`, `dated`) 
VALUES 
    (00052, 'hjpotter92', '2013-07-29');

The date with highest count (fetched by MAX(ss.count) correctly as 52) is 2013-07-29, but my select query returns me July, 26 2013.

Where am I doing wrong?

Upvotes: 0

Views: 110

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Your query is a strange mixture of aggregations. Here is a version that works:

SELECT s.`nick` AS `nick`,
    srecent.`count` AS `recent`,
    t.`total` AS `total`,
    t.`avrg` AS `avrg`,
    t.maxcount,
    s.dated
FROM (SELECT `nick`,
             SUM(`count`) AS `total`,
             AVG(`count`) AS `avrg`,
             MAX(`dated`) AS `dated`, # For most recent activity
             max(count) as maxcount
      FROM `scores`
      WHERE `nick` = 'hjpotter92'
      group by nick
     )  `t` INNER JOIN
    scores s
    ON s.nick = t.nick AND
       s.count = t.maxcount join
    scores srecent
    on srecent.nick = t.nick and
       srecent.dated = t.dated;

The problem with your original query was the max(count) in the select. This turned the outer select into an aggregation query -- one that returned one row. However, the join on ss returned multiple rows, so an arbitrary row was returned from that join. Hence the inconsistent results.

This version calculates the various aggregation values in the subquery. It then joins back to the original table to get other information from those dates.

An alternative way of formulating the query is to use the substring_index(group_concat()) trick:

SELECT `nick`,
       substring_index(group_concat(count order by dated desc), ',', 1) as mostrecent,
       SUM(`count`) AS `total`,
       AVG(`count`) AS `avrg`,
       max(count) as maxcount,
       substring_index(group_concat(dated order by count desc), ',', 1) as dated
FROM `scores`
WHERE `nick` = 'hjpotter92'
group by nick;

Upvotes: 2

Related Questions