Solved

Trying to pull distinct IDs

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

747 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

13 Experts available now in Live!

Get 1:1 Help Now