Last post Dec 05, 2011 02:59 PM by limno
Dec 01, 2011 01:40 PM|peterthegreat|LINK
i am trying to return a distinct productdescription value group on categorydesc.
for example return unique productdescription value and the corresponding categorydesc,categoryid
prodid categoryid categorydesc productdescription price
1 1 mens shirt 20
2 1 mens shirt 40
3 1 boys shirt 30
4 1 mens shirt 50
5 2 electrical computer 400
Dec 01, 2011 01:51 PM|kedarrkulkarni|LINK
from this sample data, i think category has one to many relation with productdesription....
so, will this query not give u desired result?
select categoryid, categorydesc, productdescription from tableName
group by categoryid, categorydesc, productdescription
hope this helps...
Dec 01, 2011 05:06 PM|peterthegreat|LINK
SOMEHOW I REQUIRE TO REMOVE THE DUPLICATE VALUES ON CATEGORYID,CATEGORYDESC,PRODUCTDESCRIPTION.
I ALSO REQUIRE THE PRODUCTID KEY AS THE SYSTEM INSTRUCTED.
Dec 02, 2011 12:09 AM|kedarrkulkarni|LINK
if u look at the example table u posted, for categoryid 1 and categorydescription shirt, there are two categorydesc... mens and boys
here, if if u want to het distinct on all three cols, then categorydescription will have both the rows for mens and boys and so corosponding values of other two columns too... u cant really remove duplicates from all three fields
Dec 02, 2011 11:50 AM|peterthegreat|LINK
I MEANT ON CATEGORYID AND PRODUCTDESCRIPTION.
HOW DOES INDEX COLUMNS WORK ? I ATTEMPTED TO ADD AN INDEX ON THE TWO COLUMNS AND REPLACE THE TWO COLUMNS WITH THE INDEX NAME BUT SQL WONT ACCEPT THIS.
EXAMPLE: SELECT DISTINCT CATEGORYID_PRODUCTDESCRIPTION FROM PRODUCTS
Dec 02, 2011 09:25 PM|Decker Dong - MSFT|LINK
For this situation，I think you can do to filter records by using ToTable method from DataView——
DataView dv = new DataView(DataTable);
DataTable dt = dv.ToTable(true,“categoryid”,"categorydesc");
//Then you can use dt。
Dec 03, 2011 10:28 AM|peterthegreat|LINK
but how would that retrieve distinct rows when i also require the productid key ?
my solution was to insert another column that identify proddesc. Although it mucks the infrastructure.
Someone has to do it
Dec 03, 2011 07:36 PM|Decker Dong - MSFT|LINK
As far as I see，If you add a primary key identitied from 1 by 1，you cannot do that。Because a primary key is used to identify the records from each together。So if it's included，it means that you can never find a duplicated record at all。
Dec 05, 2011 12:05 PM|peterthegreat|LINK
i know it as causing a problem with getting a distinct row.
how does the index work? i though i may define two or more columns as a index and use the name in a search query but no can do !
so in what scenario could i use such a index ?
Dec 05, 2011 02:59 PM|limno|LINK
I hope you will find this sample helpful:
create table yourProducts
(prodid int, categoryid int,
categorydesc varchar(50), productdescription varchar(50),price int)
insert into yourProducts (prodid,categoryid, categorydesc,productdescription,price )
values (1 ,1,'mens','shirt',20),
FROM (SELECT *,Row_Number()
OVER(PARTITION BY categoryid ORDER BY prodid) rn
FROM yourProducts) t
WHERE rn = 1
drop table yourProducts