Solved

SQL Server 2005 Distinct On One Column

Posted on 2010-09-02
5
179 Views
Last Modified: 2012-10-24
I need to write a query to display distinct values. Here is the scenario:

I have a Store table and a StoreSpecials table. In the Store table I have the following columns StoreID, StoreName, ParentID. In the StoreSpecials table I have the following columns StoreID and SpecialID. The ParentID equals 0 if the store is a prent and equals the ID StoreID of the parent for child stores. In the StoreSpecials table there can be many SpecialIDs for a group of stores. here is some sample data:

Store table
ID      Name         ParentID
287   Safeway   0
343   Safeway   287
526   Safeway   287

StoreSpecials table
StoreID   SpecialID
287         1
287         2
343         1
343         2
343         3
526         1
526         2
526         4

I need a query that displays the distinct SpecialIDs where the distinct SpecialIDs display for the parent instead of the children in the case where the same SpecialID is spread across multiple StoreIDs.

Thanks, Erich Lemke

0
Comment
Question by:elemke
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 33590600
can you show a sample of your expected output?
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33590685
Try This

SELECT distinct Id,SpecialID
FROM Store S INNER JOIN StoreSpecials SS
ON S.Id = SS.StoreId
WHERE S.ParentId = 0
0
 

Author Comment

by:elemke
ID: 33590869
Based on the sample data above the expected out would be:

StoreID   SpecialID   ParentID
287         1               0
287         2               0
343         3               287
526         4               287
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 250 total points
ID: 33591968
Try with row_number() windowing function and over() analytical clause.
;WITH specialsCTE
AS
(
SELECT sp.StoreID, sp.SpecialID, st.ParentID
     , ROW_NUMBER()
          OVER(PARTITION BY sp.SpecialID, 
               COALESCE(NULLIF(st.ParentID, 0), sp.StoreID)
               ORDER BY st.ParentID, sp.StoreID) rn
FROM Store st
JOIN StoreSpecials sp ON sp.StoreID = st.ID
)
SELECT StoreID, SpecialID, ParentID
FROM specialsCTE
WHERE rn = 1
;

Open in new window

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33591975
For an explanation of the above, please read this article: Analytical SQL : Where do you rank?
0

Featured Post

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

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 article I will describe the Backup & Restore 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.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

729 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