Hi All,
I have tried to create a list from a table in my database table. I have a simple query that runs when a user logs in to my site that adds the user name and a date/timestamp into two fields (user_name and date).
Now what I want is for a page that I can see to show me the last 15 login's to the site. Now I have managed to get tis query to run fine by using this query:
SELECT * FROM loggedon ORDER BY date DESC LIMIT 0, 15
Now what I want is for the list to only show the latest from each username with a maximum of 15 items displayed as the above shows a list of the last 15 login's but some of these could be from the same user.
Can anyone show me the code that might do this. I have tried using a "SELECT DISTINCT" code but this seems to do nothing. as shown below. This just shows the same information as if the "DISTINCT" was not there.
SELECT DISTINCT user_name, date FROM loggedon ORDER BY date DESC LIMIT 0, 15
Any help???
Select Distinct Query
Moderator: General Moderators
- aceconcepts
- DevNet Resident
- Posts: 1424
- Joined: Mon Feb 06, 2006 11:26 am
- Location: London
Re: Select Distinct Query
That last query looks good to me. Try removing 'date' from the query to see if you get unique usernames.
Re: Select Distinct Query
Your select distinct query is selecting rows where the combination of user_name and date are unique. So in your case it will not make any difference to the output.
As aceconcepts said, you can remove date from your query and it will return the rows you need, you just wont get the date in your outout.
Whether this is optimum I'm not sure, but it should do the job:
As aceconcepts said, you can remove date from your query and it will return the rows you need, you just wont get the date in your outout.
Whether this is optimum I'm not sure, but it should do the job:
Code: Select all
SELECT user_name, date FROM loggedon WHERE user_name IN
(SELECT DISTINCT user_name FROM loggedon)
ORDER BY date DESC LIMIT 0,15
Re: Select Distinct Query
Code: Select all
SELECT * FROM loggedon
INNER JOIN (
SELECT username,MAX(date) AS date
FROM loggedon
GROUP BY username
ORDER BY date DESC
LIMIT 0,15
) AS logged
ON logged.username = loggedon.username
AND logged.date=loggedon.date