SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Apr 2002
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL help - Eliminating duplicates

    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?

    Thanks.

  2. #2
    Are you ready for BSD? Marshall's Avatar
    Join Date
    Dec 2001
    Posts
    373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT UNIQUE account_id, type, timestamp FROM log_tbl ORDER BY timestamp DESC LIMIT 0, 20
    Would return unique `account_id' values.

    -Marshall

    Edit:

    This may not work with MySQL, but you can try it.
    Last edited by Marshall; Apr 3, 2002 at 21:25.

  3. #3
    SitePoint Member
    Join Date
    Apr 2002
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Marshall,

    You're right. It doesn't work in MySQL. But it looks like I've solved my problem:

    SELECT account_id, type, max(timestamp) AS time FROM log_tbl GROUP BY account_id, type ORDER BY time DESC LIMIT 0, 20

    Thanks!

  4. #4
    SitePoint Addict
    Join Date
    Jun 2001
    Location
    Newcastle, England
    Posts
    268
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Out of interest, the keyword is DISTINCT and not UNIQUE - however, behind the scenes MySQL itself converts this into a GROUP BY clause on all columns when it executes the statement, so I doubt there's any advantage to using it over the method you've described above. Just thought I'd mention it for future reference .
    Nick Wilson [ - email - ]


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •