Solved

Trying to pull distinct IDs

Posted on 2008-10-07
3
175 Views
Last Modified: 2010-03-19
I am trying to pull a list of distinct IDs based on maximum ContEnd_Switch date. If I change the ContEnd_Switch in the select statement to a MAX(ContEnd_Switch) and then take out the ContEnd_Switch in the Group by clause, then it won't work because I need a group by because I have ContEnd_Switch in the Order by clause. If I add Max(ContEnd_Switch) to the Having clause, for example Having Max(ContEnd_Switch) = ContEnd_Switch, then this will not work either because there are rows in the data with null ContEnd_Switch dates. Please help!
SELECT ProspectID, Prospect, ContEnd_Switch, TargetPrice, SUM(MwhAct * Total) / SUM(MwhAct) AS WAverage, 

                                GM_MarRate, Comm_1_Rate, Comm_2_Rate, Comm_3_Rate, Comm_4_Rate, Comm_5_Rate, Comm_6_Rate 

                FROM Prospect_Cogs 

                WHERE (Fwd_Mth BETWEEN 1 AND 12) and SRID = 176 and (Realtime in (select Realtime from tbl_realtime) ) 

                GROUP BY ProspectID, Prospect, TargetPrice, ContEnd_Switch, GM_MarRate, Comm_1_Rate, Comm_2_Rate, Comm_3_Rate, Comm_4_Rate, Comm_5_Rate, Comm_6_Rate 

                having SUM(MwhAct * Total) / SUM(MwhAct) > 0

                order by CASE 0

                                          when 1 then cast(ContEnd_Switch as varchar)  -- by end contract  

                                          when 2 then cast(TargetPrice as varchar)   -- Target Price 

                                          when 3 then cast(SUM(MwhAct * Total) / SUM(MwhAct) as varchar) --by quoted Price

                                          else Prospect    --by Prospect Name 

                                 END

Open in new window

0
Comment
Question by:utlonghornjulie
  • 2
3 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22661889
i don't understand your query but i think the row_number() function can help you here
0
 
LVL 3

Author Comment

by:utlonghornjulie
ID: 22662199
I am trying to pull a list of distinct ProspectIDs. For the duplicate ProspectIDs, then I want the record that has the maximum ContEnd_Switch date.
0
 
LVL 37

Accepted Solution

by:
momi_sabag earned 500 total points
ID: 22662438
so you need

with a as (
select *, row_number() over (partition by ProspectIDs order by ContEnd_Switch desc) as rown
from your_table)

select * from a where rown = 1


the partition by is like group by
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now