The distinct keyword will only return records that are distinct ie if there
are two records for visitors for the one record in the users table each with
a different date it will return 2 records. If you only want to return one
record for each record in the users table you need to work out what criteria
you are going to use to choose which of the records you want to return.
Assuming you want to get the top one by date the SQL would look something
like this:

SELECT ID, realfirst, realsecond
FROM (SELECT DISTINCT TOP 5 users.ID, users.realfirst, 
         users.realsecond, (SELECT TOP 1 visitors.date
                           FROM visitors
                           WHERE visitors.visitor = t.ID
                           ORDER BY visitors.date) date
     FROM (users 
         INNER JOIN visitors ON 
         users.ID = visitors.visitor)
         WHERE visitors.user = 1) t
ORDER BY date

regards
David Cameron
nOw.b2b
dcameron@i...

-----Original Message-----
From: David Lundin [mailto:lists@s...]
Sent: Monday, 2 July 2001 6:51 PM
To: sql language
Subject: [sql_language] RE: "Select Distinct" but still "Order By"


Sorry, having posted this message I found that this query does in fact not
return DISTINCT (users.ID) but rather one record for each record in the
"visitors" table. eg. one user will appear once for each time recorded in
the visitors table.

Does anyone have an idea on how to crack this? I have actually never used
the GROUP BY-statement and don't know how it integrates with ASP (which is
what I use). Can it be used to accomplish this?

Thanks a lot
/David


----- Original Message -----
From: "David Lundin" <lists@s...>
> Thank you very much, this did the trick!
> /David

> ----- Original Message -----
> From: "Darin Strait" <dstrait@e...>
> > How about:
> >
> > SELECT ID, realfirst, realsecond
> > FROM (SELECT DISTINCT TOP 5 users.ID, users.realfirst, users.realsecond,
> > visitors.date
> >                 FROM (users INNER JOIN visitors ON users.ID 
> > visitors.visitor)
> >                 WHERE visitors.user = 1) t
> > ORDER BY date