...

View Full Version : Select last 15 distinct records



flexillu
08-25-2011, 12:16 PM
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:


SELECT `fileName`,`time` FROM `downloads`
WHERE `userName` = 'userA'
ORDER BY `time` DESC
LIMIT 15

But how can i use DISTINCT on the filenames here?

webdev1958
08-25-2011, 12:22 PM
select distinct col1, col2 from myTable where ................

flexillu
08-25-2011, 12:35 PM
That would return where both those fields are unique, i only want the filename to be unique

webdev1958
08-25-2011, 12:40 PM
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.

flexillu
08-25-2011, 12:45 PM
Last downloads (all users):



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



UserA, file001, 14.52
UserA, file006, 14.49
UserA, file001, 14.43-Duplicate


So i want:



UserA, file001, 14.52
UserA, file006, 14.49

Old Pedant
08-25-2011, 08:32 PM
SELECT fileName,MAX(`time`) AS lastDownloadTime FROM downloads
WHERE userName = 'userA'
GROUP BY fileName
ORDER BY lastDownLoadTime DESC
LIMIT 15

webdev1958
08-26-2011, 12:37 AM
Last downloads (all users):



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



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



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.

Old Pedant
08-26-2011, 12:49 AM
Betcha because he didn't know about GROUP BY.

webdev1958
08-26-2011, 12:59 AM
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

Old Pedant
08-26-2011, 01:14 AM
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/>

webdev1958
08-26-2011, 01:35 AM
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 :)

Old Pedant
08-26-2011, 03:02 AM
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.

webdev1958
08-26-2011, 03:36 AM
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.

Old Pedant
08-26-2011, 04:01 AM
*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?

guelphdad
08-26-2011, 07:30 AM
I delete threads that are cross posted on multiple forums.

webdev1958
08-26-2011, 07:31 AM
And so far as I'm concerned, those who crosspost are evil.

There are lots who do it. I think you'll find most forums have rules against cross-posting on their individual forums but none have a rule against someone looking for help looking for it on more than one site at the same time. After all it's hard to prove that a given username on multiple sites is the same person. I suppose that all the Old Pedant usernames I have seen are the same person, are they? ;)

webdev1958
08-26-2011, 07:32 AM
I delete threads that are cross posted on multiple forums.

you mean the one's you spot?

But in any case, to me deleting threads cross posted on multiple forums seems like a stupid thing to do and let me explain why I think so.

First of all, my thinking is based on the assumption that owners of forums want to maximise their membership, especially regular contributing members.

Now, let's say there are 2 sites - site A and site B and userX pastes the same request for help on both sites at the same time. Now let's suppose a moderator on site A spots the cross-post on site B and deletes the thread on site A before a solution is posted on either site. Then userX can get a solution from site B only and if (s)he gets a solution on site B, they are much more likely to stay on site B and post there in the future, especially if userX sees that their thread on site A was deleted.

Therefore, based on the above assumption, not deleting threads cross-posted on multiple forums is a "no brainer".

flexillu
08-26-2011, 12:12 PM
Thanks for the answer, and i was very interested in getting a reply.

For the record i was asleep when you all replied...:confused:

And I wasn't aware that GROUP BY should be used here, i've used DISTINCT before and that is why i asked about that.

Thanks

Old Pedant
08-26-2011, 09:40 PM
What, you don't stay awake 24 hours waiting for answers every second?

<grin/> Sorry for the excessive commentary and glad we solved your problem.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum