select max - Dev Shed

Dev Shed Forums Sponsor:

October 22nd, 2003, 05:44 PM

gazlives

Junior Member

Join Date: Oct 2003

Location: surrey

Posts: 2

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

select max

Anybody know how to get the last entry in a row depending on the max number of another entry in the same table. These are the 2 tables:

class :
classId
className

status:
statusId
classId
...

Problem: i have a number of same classId in status table but i only want the one that 1. matches the highest statusId number and 2. matches a classId entry in the class table

I can only seem to get all of the classId back in statusId that match class classId.

selecting max(classId) in a subquery doesn't seem to work?
Is my thinking wrong - can this be done by comparing classId in 2 tables and a subquery for the max??

It must be straightforward, but i'm stumped!

anyone?

October 22nd, 2003, 06:13 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,389

Time spent in forums: 1 Month 4 Weeks 1 Day 22 h 17 m 29 sec
Reputation Power: 4080

Code:

SELECT classId, MAX(statusId)
FROM    Status WITH (NOLOCK)
GROUP BY classId

__________________
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 23rd, 2003, 03:45 PM

gazlives

Junior Member

Join Date: Oct 2003

Location: surrey

Posts: 2

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

Works great, thanks for that.

Viewing: Dev Shed Forums > Databases > MS SQL Development > select max

« 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