kenuk110
asked on
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
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>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?
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?
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)
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?
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?
See attached.
comment.txt
comment.txt
ASKER
Perfect.
Thank you.
Thank you.
Open in new window