Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

Table:

SubSection | TradingId  | Year  | Amount
100A           5001       2004  -120
200B           5001       2005  -130
300C           5002       2006  -140
300C           5003       2007  -150

I want to return the MAX Trading Id based on the subsection, I want this:

SubSection | TradingId  | Year  | Amount
100A           5001       2004  -120
200B           5001       2005  -130
300C           5003       2007  -150

I tried selecting MAX with a GROUPBY Subsection, tried HAVING MAX etc but no luck

share|improve this question
What version of Sybase? Not sure when analytics/windowing functions first appeared. – Jack Douglas♦ Jan 6 '12 at 13:21
3  
also, should we assume that SubSection, TracingId is unique? – Jack Douglas♦ Jan 6 '12 at 14:59
SubSection, TracingId are not unique – Al. Jan 6 '12 at 16:55
"are" or "is"? To be clear, I'm asking if SubSection, TracingId is unique - if the combination of the two is unique, not if either is unique on its own (which in any case wouldn't tally with your sample data). – Jack Douglas♦ Jan 6 '12 at 17:17
thanks for the help jack,SubSection, TracingId is not unique – Al. Jan 6 '12 at 17:30
show 2 more comments

2 Answers

up vote 2 down vote accepted 
SELECT t1.*
FROM the_table t1
WHERE t1.trading_id = (SELECT max(t2.trading_id)
                    FROM the_table t2
                    WHERE t2.subsection = t1.subsection)
share|improve this answer
This will return all the subsections of the MAX(trading_id) for the table. Don't think it's the same with what OP asked. – Marian Jan 6 '12 at 12:04
No. It will only get the max(trading_id) for the "current" subsection because of the co-related sub-query – a_horse_with_no_name Jan 6 '12 at 12:18
Ah, sorry, me dumb, you're right. – Marian Jan 6 '12 at 12:30
but it will return multiple rows per subsection if subsection, trading_id is not unique – Jack Douglas♦ Jan 6 '12 at 13:18
1  
@Marian quite true :-) the sample data posted is pretty small though and I'm still left doubting what he really wants - I'll ask in a comment. – Jack Douglas♦ Jan 6 '12 at 14:59
show 1 more comment
up vote 1 down vote

This will return all columns from the table, but for the max trading id of each subsection:

Select t2.*
from (
   Select max(TradingId) as MaxTradingId, SubSection
   from TableT
   group by SubSection
) t1 join TableT t2 on t1.MaxTradingId = t2.TradingId and t1.SubSection = t2.SubSection

Give it a try, please.

share|improve this answer
also assumes subsection, trading_id is unique, right? – Jack Douglas♦ Jan 6 '12 at 13:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.