Solved

Trying to pull distinct IDs

Posted on 2008-10-07
3
179 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

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

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

Suggested Solutions

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

713 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