SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Selecting TOP (1) for each group
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cnjsvision74
Starting Member

USA
4 Posts

Posted - 11/17/2012 :  15:14:06  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

433 Posts

Posted - 11/17/2012 :  18:33:25  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/17/2012 :  21:55:23  Show Profile  Reply with Quote



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

India
22772 Posts

Posted - 11/19/2012 :  04:11:35  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote

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

India
52325 Posts

Posted - 11/20/2012 :  02:42:29  Show Profile  Reply with Quote

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/

Go to Top of Page

cnjsvision74
Starting Member

USA
4 Posts

Posted - 11/21/2012 :  10:15:43  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/22/2012 :  12:05:50  Show Profile  Reply with Quote
Strange. I donot see any subquery in it.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/25/2012 :  09:14:42  Show Profile  Reply with Quote
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
Go to Top of Page

cnjsvision74
Starting Member

USA
4 Posts

Posted - 11/26/2012 :  09:44:01  Show Profile  Reply with Quote
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

USA
4 Posts

Posted - 11/26/2012 :  09:48:06  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000