Solved

Selecting distinct records?

Posted on 2010-08-21
7
828 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
kill process lock Sql server 9 54
Need help on t-sql 2012 10 54
TSQL query to generate xml 4 34
Loop through SQL parameters and insert to temp table? 4 39
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.​
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

773 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