| Author |
 Topic  |
|
|
cnjsvision74
Starting Member
USA
4 Posts |
 Posted - 11/17/2012 : 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
Posting Yak Master
Denmark
107 Posts |
Posted - 11/17/2012 : 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
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 11/17/2012 : 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
|
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48012 Posts |
Posted - 11/20/2012 : 02:42:29
|
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/
|
|
|
|
cnjsvision74
Starting Member
USA
4 Posts |
Posted - 11/21/2012 : 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.
|
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 11/22/2012 : 12:05:50
|
| Strange. I donot see any subquery in it. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48012 Posts |
Posted - 11/25/2012 : 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/
|
Edited by - visakh16 on 11/25/2012 09:19:44 |
|
|
|
cnjsvision74
Starting Member
USA
4 Posts |
Posted - 11/26/2012 : 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 |
|
|
|
cnjsvision74
Starting Member
USA
4 Posts |
Posted - 11/26/2012 : 09:48:06
|
Hi Everyone,
Thanks so much for all of your responses. I have the query working now.
Thanks again!!! |
Edited by - cnjsvision74 on 11/26/2012 10:03:49 |
|
|
| |
Topic |
|
|
|
Selecting TOP (1) for each group
