Solved

Selecting distinct records?

Posted on 2010-08-21
7
826 Views
Last Modified: 2012-08-14
Hi,

I have a query that brings back several columns of data but I really only want the data to be retrieved if certain fields are distinct.

I have attached the code I am using but I need it to be distinct on countryCode, cityCode, storeCode, gridCodeX, vendorCode .... only BUT I need the rest of the data to be returned too.

Is this possible?

Any help would be much appreciated.

Regards,

Ken
SELECT distinct	countryCode, cityCode, itemCode, storeType, vendorCode, gridCodeX, gridCodeY, facings, brand, rc, storeCode, storeName, ARABIC, 
					ItemName, upcCode, TREF, satVMF, sunVMF, monVMF, tueVMF, wedVMF, thuVMF, friVMF
FROM         dbo.ScheduleView
WHERE     (CASE DATEPART(weekday, @date ) 
                      WHEN 1 THEN [SunVMF] WHEN 2 THEN [MonVMF] WHEN 3 THEN [TueVMF] WHEN 4 THEN [WedVMF] WHEN 5 THEN [ThuVMF] WHEN 6 THEN [FriVMF] WHEN 7 THEN
                       [SatVMF] END = 1)

Open in new window

0
Comment
Question by:kenuk110
7 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 total points
Comment Utility
This should do:
SELECT * FROM (
SELECT countryCode, cityCode, itemCode, storeType, vendorCode, gridCodeX, gridCodeY, facings, brand, rc, storeCode, storeName, ARABIC, 
					ItemName, upcCode, TREF, satVMF, sunVMF, monVMF, tueVMF, wedVMF, thuVMF, friVMF,
					ROW_NUMBER() OVER ( PARTITION BY countryCode, cityCode, storeCode, gridCodeX, vendorCode ORDER BY vendorCode DESC) rnum
					FROM dbo.ScheduleView
WHERE (CASE DATEPART(weekday, @date ) 
                      WHEN 1 THEN [SunVMF] WHEN 2 THEN [MonVMF] WHEN 3 THEN [TueVMF] WHEN 4 THEN [WedVMF] WHEN 5 THEN [ThuVMF] WHEN 6 THEN [FriVMF] WHEN 7 THEN
                       [SatVMF] END = 1)) temp
WHERE rnum = 1

Open in new window

0
 
LVL 12

Expert Comment

by:NormanMaina
Comment Utility
Try the below format:
SELECT countryCode, cityCode, itemCode, storeType, vendorCode, gridCodeX, gridCodeY, facings, brand, rc, storeCode, storeName, ARABIC, 

					ItemName, upcCode, TREF, satVMF, sunVMF, monVMF, tueVMF, wedVMF, thuVMF, friVMF

FROM         dbo.ScheduleView

WHERE  countryCode=(select distinct(countryCode) and cityCode=(select distinct(cityCode) 



etc etc

Open in new window

0
 
LVL 42

Expert Comment

by:dqmq
Comment Utility
>I need it to be distinct on countryCode, cityCode, storeCode, gridCodeX, vendorCode .... only BUT I need the rest of the data to be returned too.

You need to clarify what to do when one set of distinct columns has multiple values in the other columns.  Look at a slightly simpler example:  Suppose you have two columns storeCode and ItemCode, and you want storeCode distinct.  Then, what to do with item code when it has different values in different rows for the same storeCode?






0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 12

Expert Comment

by:NormanMaina
Comment Utility

I tried to do the same thing myself and finally this is how I worked it out :



SELECT

rowCode <---- (Some unique numeral key)

firstname,lastname,address1 <---- distinct columns

plandate,plandate2 <---- non distinct columns



FROM table



WHERE rowCode IN (SELECT Min(rowCode) FROM table GROUP BY firstname,lastname,address1)

Open in new window

0
 
LVL 12

Expert Comment

by:NormanMaina
Comment Utility
Below are my test results:

select *  from ScheduleView

1      002      01      Raw Material
2      003      01      Raw Materials
3      004      02      Intermediate Products
4      002      02      Finished Products
5      002      03      Finishe Products


select *  from ScheduleView where id in(select distinct storecode from ScheduleView)

2      003      01      Raw Materials
3      004      02      Intermediate Products
4      002      02      Finished Products


Is that what you want?
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
See attached.
comment.txt
0
 

Author Closing Comment

by:kenuk110
Comment Utility
Perfect.


Thank you.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

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.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

763 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