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
 General SQL Server Forums
 New to SQL Server Programming
 Selecting TOP (1) for each group

Author  Topic 

cnjsvision74
Starting Member

4 Posts

Posted - 2012-11-17 : 15:14:06
I am new to SQL. I've inherited a report that needs modifying. I am looking to Select the TOP (1) value (DASHBOARD_STATUS_LEVEL) for each group (by MARKET_PROJECT) and place in an alias column (PROJECT_LEVEL). The SELECT statement below is a subquery that I've attempted that seems to just copy the DASHBOARD_STATUS_LEVEL into the PROJECT_LEVEL:

(SELECT TOP (1)SQL10.DASHBOARD_STATUS_LEVEL
FROM dbo.OVP_CLAIM
GROUP BY MARKET_PROJECT
ORDER BY SQL10.DASHBOARD_STATUS_LEVEL DESC)
AS PROJECT_LEVEL

MARKET_PROJECT, DASHBOARD_STATUS_LEVEL, PROJECT_LEVEL
Austin T6-2011-03157, 3, 3
Austin T6-2011-03157, 3, 3
Austin T6-2011-03157, 3, 3
Austin T6-2011-03157, 2, 2
Austin T6-2011-03157, 1, 1
Austin T6-2011-03157, 1, 1
Austin T6-2012-03751, 1, 1
Austin T6-2012-04004, 1, 1
Austin T6-2012-04004, 1, 1
Austin T6-2012-04020, 3, 3
Austin T6-2012-04020, 2, 2
Austin T6-2012-04020, 1, 1
Austin T6-2012-04020, 1, 1
Austin T6-2012-04020, 1, 1
Austin T6-2012-04020, 1, 1
Austin T6-2012-04020, 1, 1

In other words, for each record with the same MARKET_PROJECT, the PROJECT_LEVEL should be the TOP (1) DASHBOARD_LEVEL. For Austin T6-2011-03157, each record in this group should have a PROJECT_LEVEL of 3.

**Initially, I wanted the PROJECT_LEVEL to represent the MAX(DASHBOARD_STATUS_LEVEL)for each group but received errors every time I used the MAX()function.

Cjones

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2012-11-17 : 18:33:25
try this:

select a.market_project
,a.dashboard_status_level
,b.project_level
from dbo.opv_claim as a
inner join (select market_project
,max(dashboard_status_level) as project_level
from dbo.opv_claim
group by market_project
) as b
on b.market_project=a.market_project
order by a.market_project
,a.dashboard_status_level desc
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-11-17 : 21:55:23



If you are using SQL 2005 and above

Select market_project,dashboard_status_level,project_level
from
(
Select market_project
,dashboard_status_level
,project_level
,ROW_NUMBER() OVER (PARTITION BY market_project Order by dashboard_status_level desc ) as Seq
from dbo.opv_claim
)P
Where P.Seq = 1

Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-11-19 : 04:11:35

Also refer this
http://beyondrelational.com/modules/2/blogs/70/posts/10845/return-top-n-rows.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-20 : 02:42:29
[code]
Select market_project
,dashboard_status_level
,MAX(dashboard_status_level) OVER (PARTITION BY market_project) AS project_level
from dbo.opv_claim
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

cnjsvision74
Starting Member

4 Posts

Posted - 2012-11-21 : 10:15:43
quote:
Originally posted by visakh16


Select market_project
,dashboard_status_level
,MAX(dashboard_status_level) OVER (PARTITION BY market_project) AS project_level
from dbo.opv_claim


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/







Hi visakh16,

Error message received: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-11-22 : 12:05:50
Strange. I donot see any subquery in it.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-25 : 09:14:42
quote:
Originally posted by cnjsvision74

quote:
Originally posted by visakh16


Select market_project
,dashboard_status_level
,MAX(dashboard_status_level) OVER (PARTITION BY market_project) AS project_level
from dbo.opv_claim


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/







Hi visakh16,

Error message received: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.



then i'm sure you actual query is different from what you posted from above.

My above query will work fine without any syntax issues

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

cnjsvision74
Starting Member

4 Posts

Posted - 2012-11-26 : 09:44:01
quote:
Originally posted by visakh16

quote:
Originally posted by cnjsvision74

quote:
Originally posted by visakh16


Select market_project
,dashboard_status_level
,MAX(dashboard_status_level) OVER (PARTITION BY market_project) AS project_level
from dbo.opv_claim


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/







Hi visakh16,

Error message received: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.



then i'm sure you actual query is different from what you posted from above.

My above query will work fine without any syntax issues

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/





Cjones
Go to Top of Page

cnjsvision74
Starting Member

4 Posts

Posted - 2012-11-26 : 09:48:06
Hi Everyone,

Thanks so much for all of your responses. I have the query working now.

Thanks again!!!
Go to Top of Page
   

- Advertisement -