Using top 5 max (as it were!)

Question

Post reply

Using top 5 max (as it were!)

Alex Thomas

Ten Centuries

Points: 1120
More actions
July 26, 2006 at 9:55 am

#191585

Hello there,

i was wondering whether there was a simple way of getting not just the max value from a table, but the top 5 max values, for example.

My table is as such: a membership table, where a patron's new membership is added as a new record, thus they can have more than one.

I want to know for each patron, the last 5 memberships they had by date of membership, ie
Patronno, membershipno, dateofmembership, membershiptype, etc.

using max(dateofmembership) somehow, can you get the last 5?

I know there's a simple answer (i hope), but my heads about ready to implode!

Many thanks,
Alex

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ken McKelvey SSCoach Points: 18826 More actions · Answer 1

Answered recently:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=296735

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 2

select * from mytable where myid in (select top 5 myid from mytable order by mydatefield desc)

MVDBA

Alex Thomas Ten Centuries Points: 1120 More actions · Answer 3

Thanks Ken, that seems to work a treat!

Did not know you could use sub queries with an in statement to refer to the main query as it were.. you learn something knew everyday!

Alex

Alex Thomas Ten Centuries Points: 1120 More actions · Answer 4

Ok.. i used the follownig code to get what i wanted

select a.agtrefno, a.memindex from membership a

where a.memindex in (select top 5 c.memindex from membership c where c.agtrefno = a.agtrefno order by c.memdatevalid desc)

where agtrefno is the patronid, memindex is the membershipid, and memdatevalid is the validation date of the membership.

how can i get them, neatly, into a format such as

without using lots of horrible code and left joins..?

any suggestions?

thanks!

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 5

Warning... most of those sub-queries contain inequality comparsisons that form triangular joins... I haven't looked super close at them but triangular joins normally mean "Death By SQL" on larger record sets... keep an eye on them.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David Burrows SSC Guru Points: 65141 More actions · Answer 6

Put the results of your query into a temp table and use the following

SELECT a.agtrefno,

MAX(CASE WHEN a.[ID]=1 THEN a.memindex ELSE null END) AS [memindex1],

MAX(CASE WHEN a.[ID]=2 THEN a.memindex ELSE null END) AS [memindex2],

MAX(CASE WHEN a.[ID]=3 THEN a.memindex ELSE null END) AS [memindex3],

MAX(CASE WHEN a.[ID]=4 THEN a.memindex ELSE null END) AS [memindex4],

MAX(CASE WHEN a.[ID]=5 THEN a.memindex ELSE null END) AS [memindex5]

FROM (SELECT (SELECT COUNT(*) FROM #temptable c WHERE c.agtrefno=b.agtrefno AND c.memindex<=b.memindex) AS [ID],

b.agtrefno, b.memindex

FROM #temptable b) a

GROUP BY a.agtrefno

ORDER BY a.agtrefno

providing memindex is not duplicated

adding appropriate index to temp table will also speed up the query

Far away is close at hand in the images of elsewhere.
Anon.