I'm logging clicks.
I have a log_tbl in my db with the following structure:
CREATE TABLE log_tbl (
id int(10) unsigned NOT NULL auto_increment,
account_id int(10) NOT NULL default '0',
type char(12) NOT NULL default '',
timestamp int(20) NOT NULL default '0',
PRIMARY KEY (id)
I want to display a list of the last 20 accounts to be accessed, but I want the entries in the list to be unique. That is, if one account is really popular and has been accessed 6 times out of the last 20 accesses, it should only appear once in the list (preferably for the most recent access) and another account that was accessed 21 clicks ago will move into 16th position.
Oh, one thing to consider, each account_id can be of either type1 or type2 and so what I'm really looking for is unique combinations of (account_id, type) and not just account_id. If this criteria complicates things too much it can be left out. I can live with just the account_id's not differentiated by type.
I've gotten as far as this:
SELECT account_id, type, timestamp FROM log_tbl ORDER BY timestamp DESC LIMIT 0, 20
but now I need to work on eliminating the duplicates.
Can this operation be done entirely in SQL or will I have to filter out rows of a result set using PHP? The table is big, growing by about 6000 lines each day. So I probably don't want to pull the entire result set into memory just to get the first 50 or so rows.
I need a SQL guru. Does anyone have any suggestions?