Web Analytics Made Easy -
StatCounter Select last 15 distinct records - CodingForum

Announcement

Collapse
No announcement yet.

Select last 15 distinct records

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new 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
    Code:
    select distinct col1, col2 from myTable where ................

    Comment


    • #3
      That would return where both those fields are unique, i only want the filename to be unique

      Comment


      • #4
        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.

        Comment


        • #5
          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

          Comment


          • #6
            Code:
            SELECT fileName,MAX(`time`) AS lastDownloadTime FROM downloads
            WHERE userName = 'userA' 
            GROUP BY fileName
            ORDER BY lastDownLoadTime DESC
            LIMIT 15
            Be yourself. No one else is as qualified.

            Comment


            • #7
              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.

              Comment


              • #8
                Betcha because he didn't know about GROUP BY.
                Be yourself. No one else is as qualified.

                Comment


                • #9
                  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; Aug 25, 2011, 08:11 PM.

                  Comment


                  • #10
                    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/>
                    Be yourself. No one else is as qualified.

                    Comment


                    • #11
                      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

                      Comment


                      • #12
                        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.
                        Be yourself. No one else is as qualified.

                        Comment


                        • #13
                          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; Aug 25, 2011, 10:38 PM.

                          Comment


                          • #14
                            *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?
                            Be yourself. No one else is as qualified.

                            Comment


                            • #15
                              I delete threads that are cross posted on multiple forums.

                              Comment

                              Working...
                              X