Selecting distinct records?

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

kenuk110Asked:
Who is Participating?
 
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
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
 
Norman MainaCommented:
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
 
dqmqCommented:
>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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Norman MainaCommented:

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
 
Norman MainaCommented:
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
 
awking00Commented:
See attached.
comment.txt
0
 
kenuk110Author Commented:
Perfect.


Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.