SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 How do i get the Distinct parentID
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

missMac
Posting Yak Master

124 Posts

Posted - 02/11/2009 :  05:37:36  Show Profile  Reply with Quote
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

Sweden
30265 Posts

Posted - 02/11/2009 :  05:41:14  Show Profile  Visit SwePeso's Homepage  Reply with Quote
SELECT distinct	parentid
from		cs_Posts
group by	ParentID,
		threadid,
		postid,
		[subject]
having		COUNT(parentid) = 1



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

missMac
Posting Yak Master

124 Posts

Posted - 02/11/2009 :  05:49:56  Show Profile  Reply with Quote
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

Edited by - missMac on 02/11/2009 07:52:59
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 02/11/2009 :  06:21:33  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Sweden
30265 Posts

Posted - 02/11/2009 :  06:22:25  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 02/11/2009 :  07:46:04  Show Profile  Reply with Quote
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

Sweden
30265 Posts

Posted - 02/11/2009 :  07:52:50  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"

Edited by - SwePeso on 02/11/2009 07:59:21
Go to Top of Page

missMac
Posting Yak Master

124 Posts

Posted - 02/11/2009 :  07:55:19  Show Profile  Reply with Quote
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

Sweden
30265 Posts

Posted - 02/11/2009 :  07:58:33  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Sweden
30265 Posts

Posted - 02/11/2009 :  08:03:31  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"

Edited by - SwePeso on 02/11/2009 08:25:30
Go to Top of Page

missMac
Posting Yak Master

124 Posts

Posted - 02/11/2009 :  08:22:34  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000