Solved

Selecting distinct records?

Posted on 2010-08-21
7
827 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
ID: 33491831
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
ID: 33491868
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
ID: 33491912
>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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 12

Expert Comment

by:NormanMaina
ID: 33492056

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
ID: 33492068
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 32

Expert Comment

by:awking00
ID: 33492492
See attached.
comment.txt
0
 

Author Closing Comment

by:kenuk110
ID: 33495519
Perfect.


Thank you.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
In this article I will describe the Detach & Attach 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.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

912 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

16 Experts available now in Live!

Get 1:1 Help Now