Thank you for your help, but unfortunately neither of the two pieces of code
you provided did the trick.

The first SQL question,
> SELECT DISTINCT TOP 5 users.ID, users.realfirst, users.realsecond
> FROM users
>     INNER JOIN visitors ON
>     users.ID = visitors.visitor
> WHERE visitors.user1 = 1
> ORDER BY visitors.date Desc
throws the following error:
"Error: ORDER BY clause (visitors.date) conflicts with DISTINCT."
which was my problem from the start.

The second question returns a record set ordered by users.ID, just as the
code above would, if one removed the ORDER BY statement.
> SELECT DISTINCT TOP 5 users.ID, users.realfirst, users.realsecond
> FROM
>     SELECT users.ID, users.realfirst, users.realsecond
>     FROM users
>         INNER JOIN visitors ON
>         users.ID = visitors.visitor
>     WHERE visitors.user1 = 1
>     ORDER BY visitors.date Desc


I would very much appreciate any more input I can get on this. The problem
seems trivial enough, but still...

Thanks,
David Lundin

----- Original Message -----
From: "David Cameron" <dcameron@i...>
To: "sql language" <sql_language@p...>
Sent: Friday, June 29, 2001 1:38 AM
Subject: [sql_language] RE: "Select Distinct" but still "Order By"


> I have no problems running it through the syntax check in Query Analyser.
I
> did run into one other problem, user is a reserved keyword, so I had to
> rename the field.
>
> SELECT DISTINCT TOP 5 users.ID, users.realfirst, users.realsecond
> FROM users
>     INNER JOIN visitors ON
>     users.ID = visitors.visitor
> WHERE visitors.user1 = 1
> ORDER BY visitors.date Desc
>
> If this doesn't work you could always run a SELECT on a SELECT ie
>
> SELECT DISTINCT TOP 5 users.ID, users.realfirst, users.realsecond
> FROM
>     SELECT users.ID, users.realfirst, users.realsecond
>     FROM users
>         INNER JOIN visitors ON
>         users.ID = visitors.visitor
>     WHERE visitors.user1 = 1
>     ORDER BY visitors.date Desc
>
> regards
> David Cameron
> nOw.b2b
> dcameron@i...
>
> -----Original Message-----
> From: David Lundin [mailto:lists@s...]
> Sent: Thursday, 28 June 2001 5:58 PM
> To: sql language
> Subject: [sql_language] "Select Distinct" but still "Order By"
>
>
> Hello there,
>
> I've written a script which displays a list of visitors to the current
> logged-in user's personal page. All visitors to personal pages are
recorded
> in a table called "visitors" and users are stored in the table "users". I
> want to display the five last distinct people to visit the page. So if the
> five last visits at the page was from one single user, this name should
> still only appear once. Hence, I use the following SQL:
>
> SELECT DISTINCT TOP 5 users.ID, users.realfirst, users.realsecond FROM
> (users INNER JOIN visitors ON users.ID = visitors.visitor)
> WHERE visitors.user = 1
>
> visitors.user is the user whose page has been visited and visitors.visitor
> is the user who visited.
>
> Naturally, I wish to do a "ORDER BY visitors.date DESC" in order to show
the
> five last users. When I use the SQL above, the five first visitors in
order
> of users.ID are returned. It is not possible to use the "ORDER BY
> visitors.date DESC" statement, this throws an error. If I try to also
select
> visitors.date all entries are returned (e. g. several for each distinct
> user).
>
> How do I accomplish this? Grateful for your help.
>
>
> Thanks,
> David