Q:

How to use DISTINCT for just one field when ORDER BY another field is used?

I am using SQL Server Compact Edition 3.5 SP2.
I want to use this query:
SELECT DISTINCT PortalURI
FROM LoginHistory
ORDER BY LastLoginDate DESC

But it tells me that it requires LastLoginDate field to be selected too like this:
SELECT DISTINCT PortalURI, LastLoginDate
FROM LoginHistory
ORDER BY LastLoginDate DESC

But it gives me repeated PortalURI.
How can i keep PortalURI distinct?
Edit: I also used GROUP BY and ORDER BY MAX(LastLoginDate) but the result was this exception: 

Expressions in the ORDER BY list cannot contain aggregate functions.

A:

Please try:
SELECT PortalURI
FROM LoginHistory
GROUP BY PortalURI
ORDER BY MAX(LastLoginDate) DESC

EDIT: SQL Server Compact Edition does not support aggregate functions on the ORDER BY clause. So, here is an alternative solution:
SELECT PortalURI, MAX(LastLoginDate)
FROM LoginHistory
GROUP BY PortalURI
ORDER BY 2 DESC

A:

Try this:
SELECT PortalURI, MAX(LastLoginDate) LastLoginDate
FROM LoginHistory
GROUP BY PortalURI
ORDER BY LastLoginDate DESC

