I want to select the parent_topic_id from the inner query but i am getting an error though....can someone show me how i should structure these queries please?!?! thank you.....Do I not need to include the Order By or is it in the wrong place?
Msg 1033, Level 15, State 1, Line 25
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
Select sti.parent_topic_id
From
(
SELECT sti.parent_topic_id,
st.description_short,
sti.sort_order
FROM sam_topic_items sti
INNER JOIN sam_topic st ON sti.topic_id = st.id
WHERE EXISTS
(
SELECT 1
FROM sam_topic_items sti1
INNER JOIN sam_topic st1 ON sti1.topic_id = st1.id
WHERE st1.type = 'Topic'
AND sti.parent_topic_id = sti1.parent_topic_id
AND sti.sort_order = sti1.sort_order
GROUP BY sti1.parent_topic_id, sti1.sort_order, st1.Type
HAVING COUNT(sti1.sort_order) > 1
)
Order By sti.parent_topic_id
)
Put the [ORDER BY] after the last parenthesis.
|||
I still got "Incorrect Syntax near order"? What am I missing?
Select sti.parent_topic_id
From(
SELECT sti.parent_topic_id,
st.description_short,
sti.sort_order
FROM sam_topic_items sti
INNER JOIN sam_topic st ON sti.topic_id = st.id
WHERE EXISTS
(
SELECT 1
FROM sam_topic_items sti1
INNER JOIN sam_topic st1 ON sti1.topic_id = st1.id
WHERE st1.type = 'Topic'
AND sti.parent_topic_id = sti1.parent_topic_id
AND sti.sort_order = sti1.sort_order
GROUP BY sti1.parent_topic_id, sti1.sort_order, st1.Type
HAVING COUNT(sti1.sort_order) > 1
)
)
Order By sti.parent_topic_id
|||
That was a step forward.
Now the next part of the problem is that when you create a derived table, you have to give it a name. (Recall my remarks in your previous posts about using derived tables?) So add a table name after the last parenthesis and before the [ORDER BY]. (A lot of folks use dt for derived table.)
Also, since the column Parent_Topic_ID is a member of the derived table, you will have to preface the outer query to use the derived table name -NOT [sti].
<ShoulderTap>
As a note: In my opinion, you are jumping ahead too fast, and not taking the time to practice, learn, and understand the help that we are giving you. It is very important to completely understand how to properly use derived tables.
</ShoulderTap>
|||
I changed it to that and it works...I forgot all about the derived tables....opps...I am practicing as I learn....I am just trying to find out how to do things within SQL instead of minipulating SQL through VB.net or C#...You what though? Thank for you help and patience..(Did i spell that right)....
Select dt.parent_topic_id
From
(
SELECT st.id,sti.parent_topic_id,
st.description_short,
sti.sort_order
FROM sam_topic_items sti
INNER JOIN sam_topic st ON sti.topic_id = st.id
WHERE EXISTS
(
SELECT 1
FROM sam_topic_items sti1
INNER JOIN sam_topic st1 ON sti1.topic_id = st1.id
WHERE st1.type = 'Topic'
AND sti.parent_topic_id = sti1.parent_topic_id
AND sti.sort_order = sti1.sort_order
GROUP BY sti1.parent_topic_id, sti1.sort_order, st1.Type
HAVING COUNT(sti1.sort_order) > 1
)
) dt
Order By parent_topic_id
jeskey wrote: