Solved

Trying to pull distinct IDs

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to check data in sql table 11 47
SQL Agent Timeout 5 58
How can I get this column in my query? 2 46
How do I subtract date and time within a same column in SQL 4 38
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

770 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