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.
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_LEVELMARKET_PROJECT, DASHBOARD_STATUS_LEVEL, PROJECT_LEVELAustin T6-2011-03157, 3, 3Austin T6-2011-03157, 3, 3Austin T6-2011-03157, 3, 3Austin T6-2011-03157, 2, 2Austin T6-2011-03157, 1, 1Austin T6-2011-03157, 1, 1Austin T6-2012-03751, 1, 1Austin T6-2012-04004, 1, 1Austin T6-2012-04004, 1, 1Austin T6-2012-04020, 3, 3Austin T6-2012-04020, 2, 2Austin T6-2012-04020, 1, 1Austin T6-2012-04020, 1, 1Austin T6-2012-04020, 1, 1Austin T6-2012-04020, 1, 1Austin T6-2012-04020, 1, 1In 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 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-11-17 : 21:55:23
|
If you are using SQL 2005 and aboveSelect market_project,dashboard_status_level,project_levelfrom(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)PWhere P.Seq = 1 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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 MVPhttp://visakhm.blogspot.com/
Hi visakh16, Error message received: Msg 512, Level 16, State 1, Line 1Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-11-22 : 12:05:50
|
Strange. I donot see any subquery in it. |
|
|
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 MVPhttp://visakhm.blogspot.com/
Hi visakh16, Error message received: Msg 512, Level 16, State 1, Line 1Subquery 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 MVPhttp://visakhm.blogspot.com/ |
|
|
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 MVPhttp://visakhm.blogspot.com/
Hi visakh16, Error message received: Msg 512, Level 16, State 1, Line 1Subquery 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 MVPhttp://visakhm.blogspot.com/
Cjones |
|
|
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!!! |
|
|
|
|
|
|
|