?
Solved

Selecting distinct records?

Posted on 2010-08-21
7
Medium Priority
?
834 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 2000 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

840 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