select top 3 - Dev Shed

Dev Shed Forums Sponsor:

October 9th, 2003, 06:30 PM

codejockey

Junior Member

Join Date: Oct 2003

Posts: 3

Time spent in forums: < 1 sec
Reputation Power: 0

select top 3

I have a table that holds comment records. The columns are :
user_id, comment, date

How can I write a select statement that returns the 3 most recent comment records for each user, all in one recordset.

thanks for any help.

October 9th, 2003, 06:36 PM

Scorpions4ever

Banned ;)

Dev Shed God 9th Plane (9000 - 9499 posts)

Join Date: Nov 2001

Location: Woodland Hills, Los Angeles County, California, USA

Posts: 9,382

Time spent in forums: 1 Month 4 Weeks 1 Day 20 h 31 m 48 sec
Reputation Power: 4080

SELECT TOP 3 *
FROM table
ORDER BY date DESC

__________________
Up the Irons
What Would Jimi Do? Smash amps. Burn guitar. Take the groupies home.
"Death Before Dishonour, my Friends!!" - Bruce D ickinson, Iron Maiden Aug 20, 2005 @ OzzFest
Down with Sharon Osbourne

October 9th, 2003, 06:45 PM

codejockey

Junior Member

Join Date: Oct 2003

Posts: 3

Time spent in forums: < 1 sec
Reputation Power: 0

scorp,
this returns the top 3 across all users. i need to get the top 3 for each user. i've done it with a cursor, but i'd like to find a more efficient way.
cj

October 10th, 2003, 02:49 AM

swampBoogie

Contributing User

Join Date: Jan 2003

Location: Paris Uppland

Posts: 2,349

Time spent in forums: 1 Month 4 Days 7 h 10 m 28 sec
Reputation Power: 390

Code:

select * from t as q
where date in (
SELECT TOP 3 date
FROM t
where t.user_id = q.user_id
ORDER BY date DESC)

October 10th, 2003, 07:39 AM

codejockey

Junior Member

Join Date: Oct 2003

Posts: 3

Time spent in forums: < 1 sec
Reputation Power: 0

Thanks swampBoogie, it worked.

Viewing: Dev Shed Forums > Databases > MS SQL Development > select top 3

« Previous Thread | Next Thread »

Developer Shed Advertisers and Affiliates

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump