CODEWITHSUNDEEP
mysqldistinct
Asim Zaidi
Asim Zaidi

Reputation: 28284

EDITED:: distinct multiple fields

I have this query

SELECT distinct username, time
FROM `sadaat`.`wp_loginlog`
where username != 'admin' or username is not null
order by time DESC
limit 0,20

This doesnt give me the distinct records. If I take out time then it does. What can I do to get distinct results there.

if I use 

SELECT distinct username
FROM `sadaat`.`wp_loginlog`
where username != 'admin' or username is not null
order by time DESC
limit 0,20

then I can not get the value for time as it will not select the time field

Upvotes: 0

Views: 1292

Answers (3)

Asim Zaidi
Asim Zaidi

Reputation: 28284

here is what did the trick

   SELECT *
    FROM `sadaat`.`wp_loginlog`
    where username != 'admin' or username is not null
Group By username
    order by time DESC
    limit 0,20

Upvotes: 0

Jon
Jon

Reputation: 4945

Your current query is not working because distinct takes into account all the columns you are selecting.

If you want distinct usernames while including the time column, you can use group by with an aggregate for the time column, something like:

SELECT username, max(time)
FROM `sadaat`.`wp_loginlog` where username != 'admin' or username is not null
group by username
order by time DESC limit 0,20

Upvotes: 2

ArtoAle
ArtoAle

Reputation: 2977

The DISTINCT clause refer to all the attributes in the SELECT clause, not just username. (actually, it does not make sense to have distinct username and non-distinct time).

For instance if you have row with the same username and different time they will always be displayed

Maybe, if you want some information about time relevant to each single user you can have a look at the group by clause

Upvotes: 2

Related Questions