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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-12-14 : 08:02:34
|
| Simon Fallows writes "Ok her is a real challenge - and what really bugs me is this should be simple because so many people must face it in different ways.....I have a bunch of users, and a table with lots of records for each user. Each record is timestamped, and has half a dozen columns, all of which could be anything(but I need all of the cols values)...What I want to do is bring back all columns for the "latest set" of records for each user in a list. In my case its a series of online test scores, so one of my columns is "course id" and each user may have several records for each course id (i.e. 3/10 fail, 5/10 fail 8/10 pass etc)But the problem is similar to someone who has to pull back a group of items from the latest purchase order for example...I know this is not correct SQL, but feel it should beSelect latest(*) from records where user_id in (...) and course id in(...).I eventually ended up pulling all records back and ordering by user, courseid and date & dumping older records - memory intensive, but at least my code was legible!!OK Guys - are you up for the challenge? - remember it has to be elegant - not just a massive pile of mud....yours pessimisticallysf." |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-12-14 : 08:08:14
|
use exists."latest" is defined with your timestap.Go with the flow & have fun! Else fight the flow  blog thingie: http://weblogs.sqlteam.com/mladenp |
 |
|
|
|
|
|
An elegant "Latest" records query - I defy you