Author |
Topic |
missMac
Posting Yak Master
124 Posts |
Posted - 2009-02-11 : 05:37:36
|
Hello guys,how do i get the distinct parentID in my select statementhere is my codethanksselect threadid, postid, [Subject], parentid from cs_Posts group by ParentID, threadid, postid, [subject] having COUNT(parentid) = 1 order by postid desc |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-02-11 : 05:41:14
|
[code]SELECT distinct parentidfrom cs_Postsgroup by ParentID, threadid, postid, [subject]having COUNT(parentid) = 1[/code] E 12°55'05.63"N 56°04'39.26" |
|
|
missMac
Posting Yak Master
124 Posts |
Posted - 2009-02-11 : 05:49:56
|
Doest work pesoresults123578910111213151617181920212223242627282930313233343536373839404142444547484950515254565758 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-02-11 : 06:21:33
|
They seem to be DISTINCT to me... E 12°55'05.63"N 56°04'39.26" |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
missMac
Posting Yak Master
124 Posts |
Posted - 2009-02-11 : 07:46:04
|
If you read the first sql statement i posted, i want to retrieve not only "ONE" column, but other columns as well.YOur statement only gets one columnselect threadid, postid, [Subject], parentid from cs_Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-02-11 : 07:52:50
|
My bad. I read that you wanted distinct ParentIDquote: Originally posted by missMac how do i get the distinct parentID in my select statement
You have to decide what to do with duplicates.ThreadID PostID ParentID Subject-------- ------ -------- ---------------------1 1 1 I don't know III2 3 1 I don't know IV Which line do you want to keep since both have ParentID 1? E 12°55'05.63"N 56°04'39.26" |
|
|
missMac
Posting Yak Master
124 Posts |
Posted - 2009-02-11 : 07:55:19
|
Ok thanks.I want to keep only "ONE" instance of parent id, which should be the first occurrence only. Thanks |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-02-11 : 07:58:33
|
There is no such concept as "first" in a relational databas.Please define what "first" means to you.1) Any one random record?2) Sorted by ThreadID, PostID, Subject?3) Sorted by a datetime column?4) Sorted by a key or identity column? E 12°55'05.63"N 56°04'39.26" |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-02-11 : 08:03:31
|
Here is how you get any one random record for each ParentIDSELECT ThreadID, PostID, [Subject], ParentIDFROM ( SELECT ThreadID, PostID, [Subject], ParentID, ROW_NUMBER() OVER (PARTITION BY ParentID ORDER BY NEWID()) AS recID FROM ( SELECT ThreadID, PostID, [Subject], ParentID FROM cs_Posts GROUP BY ThreadID, PostID, [Subject], ParentID HAVING COUNT(ParentID) = 1 ) AS YourOldQuery ) AS dWHERE recID = 1ORDER BY PostID DESC E 12°55'05.63"N 56°04'39.26" |
|
|
missMac
Posting Yak Master
124 Posts |
Posted - 2009-02-11 : 08:22:34
|
thanks,I had to modify it a bitSELECT ThreadID, PostID, [Subject], ParentID, totalviews FROM ( SELECT ThreadID, PostID, [Subject], ParentID, totalviews, ROW_NUMBER() OVER (PARTITION BY threadID ORDER BY NEWID()) AS recID, COUNT(*) OVER (PARTITION BY ThreadID, PostID, [Subject], ParentID,totalviews) AS cnt FROM cs_Posts ) AS dWHERE recID = 1 AND cnt = 1ORDER BY TotalViews DESC But thanks a great deal, much appreciated |
|
|
|