Distinct
This is a discussion on Distinct within the MySQL Database forums, part of the Database Forums category; Hi :) i don't know how to get the last record of each distinct entries in one of my table ...
|
|||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
12-22-2007
|
|||
|
|||
Distinct
Hi :)
i don't know how to get the last record of each distinct entries in one of my table connexion. The table contains trace of connexions on a website. The fields are id, date, hour, user, browser, pageviewed. If i use "SELECT DISTINCT(user) FROM connexions ORDER BY date DESC, heure DESC" i get just the name of the each user If i use "SELECT DISTINCT(user),date,hour FROM connexions ORDER BY date DESC, heure DESC" i get more than one line per user !? What's the right way to obtain a list of each user last connexion ? Thanks in advance ! 
|
|
12-22-2007
|
|||
|
|||
|
Re: Distinct
Axelar wrote:
> Hi :) > > i don't know how to get the last record of each distinct entries in > one of my table connexion. The table contains trace of connexions on a > website. The fields are id, date, hour, user, browser, pageviewed. > > If i use "SELECT DISTINCT(user) FROM connexions ORDER BY date DESC, > heure DESC" i get just the name of the each user > > If i use "SELECT DISTINCT(user),date,hour FROM connexions ORDER BY > date DESC, heure DESC" i get more than one line per user !? > > What's the right way to obtain a list of each user last connexion ? > > Thanks in advance ! Try SELECT user, date, hour FROM connexions ORDER BY date DESC , hour DESC GROUP BY user -- //Aho
|
|
12-22-2007
|
|||
|
|||
|
Re: Distinct
On 22 déc, 12:14, "J.O. Aho" <u...@example.net> wrote:
> Axelar wrote: > > Hi :) > > > i don't know how to get the last record of each distinct entries in > > one of my table connexion. The table contains trace of connexions on a > > website. The fields are id, date, hour, user, browser, pageviewed. > > > If i use "SELECT DISTINCT(user) FROM connexions ORDER BY date DESC, > > heure DESC" i get just the name of the each user > > > If i use "SELECT DISTINCT(user),date,hour FROM connexions ORDER BY > > date DESC, heure DESC" i get more than one line per user !? > > > What's the right way to obtain a list of each user last connexion ? > > > Thanks in advance ! > > Try > SELECT user, date, hour FROM connexions ORDER BY date DESC , hour DESC GROUP > BY user > > -- > > //Aho Seems like i can't use GROUP BY after ORDER BY
|
|
12-22-2007
|
|||
|
|||
|
Re: Distinct
Axelar wrote:
> Hi :) > > i don't know how to get the last record of each distinct entries in > one of my table connexion. The table contains trace of connexions on a > website. The fields are id, date, hour, user, browser, pageviewed. > > If i use "SELECT DISTINCT(user) FROM connexions ORDER BY date DESC, > heure DESC" i get just the name of the each user > > If i use "SELECT DISTINCT(user),date,hour FROM connexions ORDER BY > date DESC, heure DESC" i get more than one line per user !? > > What's the right way to obtain a list of each user last connexion ? > > Thanks in advance ! Yawn, once again the answer is the "strawberry query". If I had a penny for every time I have answered this question! http://dev.mysql.com/doc/refman/5.0/...group-row.html the query you are looking for is the one with the LEFT JOIN.
|
Linear Mode
