| Author |
 Topic  |
|
|
missMac
Posting Yak Master
124 Posts |
 Posted - 02/11/2009 : 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
Sweden
29138 Posts |
Posted - 02/11/2009 : 05:41:14
|
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" |
 |
|
|
missMac
Posting Yak Master
124 Posts |
Posted - 02/11/2009 : 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
|
Edited by - missMac on 02/11/2009 07:52:59 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 02/11/2009 : 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
Sweden
29138 Posts |
|
|
missMac
Posting Yak Master
124 Posts |
Posted - 02/11/2009 : 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 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 02/11/2009 : 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 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 |
|
|
|
missMac
Posting Yak Master
124 Posts |
Posted - 02/11/2009 : 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
Sweden
29138 Posts |
Posted - 02/11/2009 : 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
Sweden
29138 Posts |
Posted - 02/11/2009 : 08:03:31
|
Here is how you get any one random record for each ParentIDSELECT 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 |
|
|
|
missMac
Posting Yak Master
124 Posts |
Posted - 02/11/2009 : 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 |
|
|
| |
Topic |
|
How do i get the Distinct parentID
