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 |
|
Robx
Starting Member
4 Posts |
 Posted - 2007-10-04 : 06:57:24
|
| HiJust wondered if someonecould point me in the right direction with an sql query im trying to create. I am extremely new to writing SQL queries so bare with me...If I have a simple table example:Record ID Adviser Result (Pass or Fail)I’m trying to produce a query that will extract the last 10 records for each adviser (based on Record ID). I have tried using select top 10 but obviously its just bringing out the last 10 records when I actually want the last 10 for each adviser…If you can throw some light on where I need to be going with this one because its frazzling my brain..!CheersRob |
|
|
Robx
Starting Member
4 Posts |
Posted - 2007-10-04 : 07:00:32
|
| Just to clarify as the fields may look confusing:Record ID / Adviser / Result (Pass or Fail) |
 |
|
|
Robx
Starting Member
4 Posts |
Posted - 2007-10-04 : 10:02:52
|
| OK, as I couldn't come up with any fancy SQL code I had to do it the long way round..Create a query that just pulls out the last record for each adviser by using group by and max of record ID.Create another query that does the same as above but the ID < the ID in the first query.Create another query that does the same as above but the ID < the ID in the second query.And so on, until I have 10 queries all pulling out the max ID < Max ID of the previous query. Chuck all the queries in a single SQL view using union and bingo a query showing the last 10 records for each adviser.Would still be interested if someone could come up with a more efficient way of doing this... |
|
|
|
Bit Wrangler
Starting Member
9 Posts |
Posted - 2007-11-02 : 09:24:26
|
| There is some SQL trickery to do this:SELECT * FROM RecordsTable R WHERE RecordID IN (SELECT TOP 10 RecordID FROM RecordsTable WHERE Adviser = R.Adviser ORDER BY RecordID DESC)It's more succinct, but your approach may actually perform better depending on the size of your table. If you add "AND Adviser = 'x'" to the WHERE clause, it should be pretty fast all around. |
|
|
|
Robx
Starting Member
4 Posts |
Posted - 2007-11-05 : 04:24:05
|
| Thanks for giving my problem some thought mate, I'll give your suggestion a try..! |
|
|
|
|
|
|
Access 97 - SQL help