Getting only the top number from a dataset

Question

Post reply

Getting only the top number from a dataset

Stephen crocker

SSCrazy

Points: 2512
More actions
October 21, 2009 at 8:35 am

#135789

I have a query I need to build and am pulling my hair out over it.
I have a table that has multiple records for each person in the db.
It is a total number of years that they have been a member,
I am able to return the number of records I expect but need to order 2 columns,
1 the number of years (desc)
2. and the cst_key
I just need to return the top 1 record for each person in the query so I can get the most recent number of years.
Anyone have any advice they can offer?
Appreciate it very much

Viewing 4 posts - 1 through 3 (of 3 total)

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

matt6288 SSCertifiable Points: 5058 More actions · Answer 1

Table definitions and sample data would help but this might get you started.

select * from person P

where numOfYears = (select max(numOfYears) from person P2 where P.cus_key = P2.cus_key)

This assumes you only have 1 record per year.

Stephen crocker SSCrazy Points: 2512 More actions · Answer 2

Matt Wilhoite (10/21/2009)
Table definitions and sample data would help but this might get you started.
select * from person P
where numOfYears = (select max(numOfYears) from person P2 where P.cus_key = P2.cus_key)
This assumes you only have 1 record per year.

Thanks Matt, we have been wrestling with this for almost 2 years, I will give that a shot and let you know if it works or not.

Nabha SSCrazy Eights Points: 8534 More actions · Answer 3

Stephen crocker (10/21/2009)
I have a query I need to build and am pulling my hair out over it.
I have a table that has multiple records for each person in the db.
It is a total number of years that they have been a member,
I am able to return the number of records I expect but need to order 2 columns,
1 the number of years (desc)
2. and the cst_key
I just need to return the top 1 record for each person in the query so I can get the most recent number of years.
Anyone have any advice they can offer?
Appreciate it very much

Another way would be,

SELECT * FROM

(SELECT * ,

ROW_NUMBER() OVER (PARTITION BY PERSON ORDER BY YEAR, cst_key) as Row_no) t

Where row_no = 1

If its not clear, post the test data, table create script and expected result. Thanks.

---------------------------------------------------------------------------------