Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 How do i get the Distinct parentID

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 statement

here is my code

thanks

select 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 parentid
from cs_Posts
group by ParentID,
threadid,
postid,
[subject]
having COUNT(parentid) = 1[/code]


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

missMac
Posting Yak Master

124 Posts

Posted - 2009-02-11 : 05:49:56
Doest work peso
results


1
2
3
5
7
8
9
10
11
12
13
15
16
17
18
19
20
21
22
23
24
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
44
45
47
48
49
50
51
52
54
56
57
58
Go to Top of Page

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"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-11 : 06:22:25
Before you post anything more in this topic I'd suggest you to read this
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

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 column

select threadid, postid, [Subject], parentid from cs_Posts
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-11 : 07:52:50
My bad. I read that you wanted distinct ParentID
quote:
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 III
2 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"
Go to Top of Page

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
Go to Top of Page

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"
Go to Top of Page

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 ParentID
SELECT		ThreadID,
PostID,
[Subject],
ParentID
FROM (
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 d
WHERE recID = 1
ORDER BY PostID DESC



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

missMac
Posting Yak Master

124 Posts

Posted - 2009-02-11 : 08:22:34
thanks,
I had to modify it a bit

SELECT 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 d
WHERE recID = 1
AND cnt = 1
ORDER BY TotalViews DESC


But thanks a great deal, much appreciated
Go to Top of Page
   

- Advertisement -