TOP, DISTINCT and ORDER BY - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

TOP, DISTINCT and ORDER BY

Author

Topic

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-04-06 : 13:53:44

I was asked by a friend of mine how to select the last 3 people that have logged in to the website and I'm quite embarassed but it seems I just can't do it even if my life depended on it. DDL:

DECLARE @myTable table (VisitorID int, Login datetime)

insert into @myTable (VisitorID, Login)
SELECT 1, '2004-03-01 01:01:01' UNION ALL SELECT 2, '2004-03-02 01:01:01' UNION ALL
SELECT 3, '2004-03-03 01:01:01' UNION ALL SELECT 1, '2004-03-04 01:01:01' UNION ALL
SELECT 4, '2004-03-01 01:01:01' UNION ALL SELECT 5, '2004-03-02 01:01:01' UNION ALL
SELECT 4, '2004-03-03 01:01:01' UNION ALL SELECT 5, '2004-03-04 01:01:01' UNION ALL
SELECT 1, '2004-03-05 01:01:01' UNION ALL SELECT 2, '2004-03-05 01:01:01'

EDIT: THis is what I have come up with so far but naturally it fails:
SELECT DISTINCT TOP 3 VisitorID FROM @myTable ORDER BY Login DESC

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-04-06 : 13:57:52

[code]
select top 3 visitorID from
(select visitorID, max(login) as MaxLogin from @myTable group by VisitorID) a
order by MaxLogin DESC
[/code]

- Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-06 : 13:59:32

Maybe I'm missing something:

SELECT DISTINCT TOP 3 VisitorID, Login
FROM @myTable
ORDER BY Login DESC

Using your sample data, what is the expected result set?

Tara

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-04-06 : 14:00:18

Awesome...thanx man...don't think I could have come up with that solution...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-06 : 14:01:42

Ah, you don't want the Login column to be displayed.

Tara

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-04-06 : 14:05:45

Yup Tara, the Login will be distinct for just about every entry so using this would cause the same userid to appear several times in the list. Jeff took care of it though :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources