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.
| Author |
Topic |
|
joxer
Starting Member
2 Posts |
 Posted - 2008-09-04 : 17:02:23
|
| Hi, (hope to be in the right topic)it's maybe simple, but I've been militating against it several hours.I have a school-class web and there is a database with every visit of every member. Now I want to have "SELECTED" last 5 visitors and the time.Ex: name - page - time Peter - index - 16:20 Peter - sched - 16:21 Marin - index - 17:44 Marin - histo - 17:46 Edwin - subje - 17:48 Marin - sched - 17:48and I need this result:Marin - 17:48 Edwin - 17:48Peter - 16:21 |
|
|
dexter.knudson
Constraint Violating Yak Guru
260 Posts |
Posted - 2008-09-04 : 17:13:56
|
| Have a look at the "SELECT TOP", "ORDER BY" & "GROUP BY" clauses. You will need a sub query. A primary key (eg identity column) would also be helpful. |
 |
|
|
joxer
Starting Member
2 Posts |
Posted - 2008-09-04 : 17:53:48
|
| I know but I can't construct the query... I've tried all possibilities with ORDER BY & GROUP BY :( And I hav no idea how to write down the sub query |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-09-04 : 17:57:31
|
No subquery needed, but rather a derived table. They are different concepts.SELECT TOP 5 name, timeFROM( SELECT name, MAX(time) AS time FROM YourTable GROUP BY name) dtORDER BY time DESC Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
dexter.knudson
Constraint Violating Yak Guru
260 Posts |
Posted - 2008-09-05 : 02:04:05
|
| Note that this is dependent on your names being unique for each visitor. If you need to cater for different visitors having the same name, you will need a unique idenifier for each visitor. |
|
|
|
|
|
|
Last 5 visits of my page