Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    New Coder
    Join Date
    Aug 2010
    Posts
    89
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Select last 15 distinct records

    I have a download log table.

    I'm trying to return the last 15 files downloaded by the user, but i want the filenames to be unique. So if they download file 001.jpeg three times, i only want that to appear once in the query result.

    Here's my code to return the last 15:

    Code:
    SELECT `fileName`,`time` FROM `downloads`
    WHERE `userName` = 'userA' 
    ORDER BY `time` DESC
    LIMIT 15
    But how can i use DISTINCT on the filenames here?

  • #2
    Banned
    Join Date
    Apr 2011
    Posts
    656
    Thanks
    14
    Thanked 69 Times in 69 Posts
    Code:
    select distinct col1, col2 from myTable where ................

  • #3
    New Coder
    Join Date
    Aug 2010
    Posts
    89
    Thanks
    3
    Thanked 0 Times in 0 Posts
    That would return where both those fields are unique, i only want the filename to be unique

  • #4
    Banned
    Join Date
    Apr 2011
    Posts
    656
    Thanks
    14
    Thanked 69 Times in 69 Posts
    that's what you asked for when you said
    So if they download file 001.jpeg three times, i only want that to appear once in the query result.
    Post a sample output listing.

  • #5
    New Coder
    Join Date
    Aug 2010
    Posts
    89
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Last downloads (all users):

    Code:
    UserA, file001, 14.52
    UserB, file003, 14.51
    UserA, file006, 14.49
    UserA, file001, 14.43..etc
    I want the last X downloads by userA but i don't want duplicate filenames

    Code:
    UserA, file001, 14.52
    UserA, file006, 14.49
    UserA, file001, 14.43-Duplicate
    So i want:

    Code:
    UserA, file001, 14.52
    UserA, file006, 14.49

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,567
    Thanks
    78
    Thanked 4,384 Times in 4,349 Posts
    Code:
    SELECT fileName,MAX(`time`) AS lastDownloadTime FROM downloads
    WHERE userName = 'userA' 
    GROUP BY fileName
    ORDER BY lastDownLoadTime DESC
    LIMIT 15
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    Banned
    Join Date
    Apr 2011
    Posts
    656
    Thanks
    14
    Thanked 69 Times in 69 Posts
    Quote Originally Posted by flexillu View Post
    Last downloads (all users):

    Code:
    UserA, file001, 14.52
    UserB, file003, 14.51
    UserA, file006, 14.49
    UserA, file001, 14.43..etc
    I want the last X downloads by userA but i don't want duplicate filenames

    Code:
    UserA, file001, 14.52
    UserA, file006, 14.49
    UserA, file001, 14.43-Duplicate
    So i want:

    Code:
    UserA, file001, 14.52
    UserA, file006, 14.49
    if that's what you want then why are you asking how to use DISTINCT?

    You need to GROUP BY the file name instead.

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,567
    Thanks
    78
    Thanked 4,384 Times in 4,349 Posts
    Betcha because he didn't know about GROUP BY.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #9
    Banned
    Join Date
    Apr 2011
    Posts
    656
    Thanks
    14
    Thanked 69 Times in 69 Posts
    Quote Originally Posted by Old Pedant View Post
    Betcha because he didn't know about GROUP BY.
    We could go on speculating until eternity because only the op knows the answer.

    Maybe (s)he has heard of GROUP BY but doesn't know what it does or how to use it.

    Maybe (s)he doesn't properly know what DISTINCT does.

    etc

    etc
    Last edited by webdev1958; 08-26-2011 at 12:11 AM.

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,567
    Thanks
    78
    Thanked 4,384 Times in 4,349 Posts
    Oh, I don't think it would last until eternity. I'm sure the number of possible reasons is finite. Large, but still finite. <grin/>
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #11
    Banned
    Join Date
    Apr 2011
    Posts
    656
    Thanks
    14
    Thanked 69 Times in 69 Posts
    it depends on whether you mean plausible or you include implausible possibilities.

    But in either case technically it could take to eternity (which will never be reached by definition) because of the potential "eternity" between coming up with possibilities

    But I don't see the point you are trying to make.

    I asked the question to the op, not you
    because you don't know the answer and the op is the only one who does. If I thought you might know the answer, I would have asked you as well

  • #12
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,567
    Thanks
    78
    Thanked 4,384 Times in 4,349 Posts
    Well, I had already shown the code using GROUP BY, so I thought it was also a comment on my code. But since he/she doesn't seem interested in getting an answer, it's all moot anyway.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #13
    Banned
    Join Date
    Apr 2011
    Posts
    656
    Thanks
    14
    Thanked 69 Times in 69 Posts
    But since he/she doesn't seem interested in getting an answer.....
    How do you know? - Surely you're not suggesting the world revolves around CF are you? I think you'll find the op probably got help on another forum

    If you go look at the activity on sitepoint, the activity on CF is very much less.
    Last edited by webdev1958; 08-26-2011 at 02:38 AM.

  • #14
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,567
    Thanks
    78
    Thanked 4,384 Times in 4,349 Posts
    *sigh* Okay, not interested in getting an answer *HERE*.

    And so far as I'm concerned, those who crosspost are evil. If I find somebody doing it more than a couple of times, then I stop answering that person.

    Would you like me to just wipe out all my silly comments in this thread?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #15
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    I delete threads that are cross posted on multiple forums.


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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