Solved

SQL Server 2005 Distinct On One Column

Posted on 2010-09-02
5
174 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
5 Comments
 
LVL 69

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 59

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 59

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.

932 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

11 Experts available now in Live!

Get 1:1 Help Now