Take the 2-minute tour ×
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.

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

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
add comment

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.