Need to SELECT TOP on FOR EACH basis...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need to SELECT TOP on FOR EACH basis...

Author

Topic

jwalborn
Starting Member

2 Posts

Posted - 2009-10-09 : 19:07:22

I'm sure most of you have seen some form of this before... I need to select the top (n) rows for each group in a query. I've seen several examples online, but they all used a single table and I can't get them to work for my situation.

I'll simplify as much as possible and present the problem with only two tables [ASSET] and [DATAPOINT].

[ASSET]
id,name
1,'assetone'
2,'assettwo'
3,'assetthree'

[DATAPOINT]
id,name,value,asset_id
101,'dp101','aaa',1
102,'dp102','bbb',1
103,'dp103','ccc',1
201,'dp201','ddd',2
202,'dp202','eee',2
301,'dp301','fff',3
302,'dp302','ggg',3
303,'dp303','hhh',3

I need to select the top (n) [DATAPOINT] values for each [ASSET]. For the sake of an example, we can return the TOP 1 value for each asset, needing to end up with a result like:

asset_id,asset_name,datapoint_id,datapoint_name,datapoint_value
1,'assetone',103,'dp103','ccc'
2,'assettwo',202,'dp202','eee'
3,'assetthree',303,'dp303','hhh'

We're obviously going to have to use grouping and probably some sort of self-relationship within the [ASSET] table, but I can't quite figure out how to resolve this.

Any thoughts?

-----

I'd like to avoid temporary tables and / or cursors if possible. If we have to go that route, we'll solve this one application-side.

Ask not what your country can do for you;
'Cause you're not gonna like the answer...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-09 : 21:57:16

see http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

KH
[spoiler]Time is always against us[/spoiler]

jwalborn
Starting Member

2 Posts

Posted - 2009-10-12 : 10:56:29

Perfect, thanks... I was extremely close to this solution myself, but couldn't quite sort it out in my head. Using the subquery to establish the row_count was the key that unlocked the whole thing... I was having trouble integrating the row_count into the WHERE clause previous. Should have thought of that myself.

Thank you again!

Ask not what your country can do for you;
'Cause you're not gonna like the answer...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources