Return Duplicate Rows

I want to select all my columns that have the same number twice....but i dont know how....I tried doit it this way

Select Distinct sti.parent_topic_id, st.description_short, sti.sort_order, count(st.description_short)

From sam_topic_items sti

Inner Join sam_topic st On sti.topic_id = st.id

Where st.type = 'Topic'

Group By sti.parent_topic_id, st.description_short, sti.sort_order

--Having count(sti.sort_order) > 1

Order By parent_topic_id

I even tried sti.sort_order = sti.sort_order and that does not work eithier

But it returns everything...when i uncomment the having clause it does not return anything....I want my end result to be

2 TheName 10 1

2 SomthingElse 10 1

You see they have the same id and the same sort_order but diffrent description names.......when i run the above query it returns everything for some reason...I am not clear on how i need to set the criteria to only pull those rows with the same sort_order number for a particular ID? any help?

select *
from sam_topic_items sti

inner join sam_topic st

on sti.topic_id = st.id
inner join
(
select sti.topic_id
from sam_topic_items sti
inner join sam_topic st
on sti.topic_id = st.id
where st.type = 'Topic'
group by sti.topic_id
having count(*) > 1
) d
on st.topic_id = d.topic_id|||

Thanks 4 the response but this returns all my rows with the sam ID's instead of the same sort order...I have a table like this

id sort_order

1 2

1 2

1 1

1 3

I only want to return.......

id sort_order

1 2

1 2

I thought if i did it like you had it but tweak it a little it would work....but it still returns all the rows

select *

from sam_topic_items sti

inner join sam_topic st

on sti.parent_topic_id = st.id

inner join

(

select sti.parent_topic_id, sti.sort_order

from sam_topic_items sti

inner join sam_topic st

on sti.parent_topic_id = st.id

where st.type = 'Topic'

group by sti.parent_topic_id, sti.sort_order

having count(*) > 1

) d

on sti.sort_order = d.sort_order

|||

I think you need to join by both sort_order and topic_id.

A more basic question is if you know where the duplication is coming from.

Are there multiple records in the topic table (of type 'topic') which have the same ID?|||There are multiple items in the topic_item tables that have the same number in thier sort order...and I only want to select those id's that have the same number in there sort_order....my topic table has a PK of id and my topic_items table has a PK of topic ID and a PK of parent_topic_id.....|||

When I say primary key I mean a column (or columns) which uniquely identify a single record in the table. That's means there cannot be duplicates in the primary key. I am interested in topic_id vs. parent_topic_id are these the same field or related fields. They look like foreign keys (fields which link to a primary key on another table).

The main issue in your query seems to be multiple records in the topic_items table with the same topic_ID (or parent_topic_ID I'm not sure) and the same sort order.

If this is the case then:

SELECT topic_ID, sort_order
FROM topic_items
GROUP BY topic_ID, sort_order
HAVING count(*) > 1

should give a list of these records (but only 1 instance of each combination). Then joining it back to the other tables should give the record list you want.

SELECT ST.ID, STI.description_short, STI.sort_order
FROM (
SELECT topic_ID, sort_order
FROM sam_topic_items
GROUP BY topic_ID, sort_order
HAVING count(*) > 1
) TS
INNER JOIN sam_topic_items STI
ON (TS.topic_ID = STI.topic_ID) AND
(TS.sort_order = STI.sort_order)
INNER JOIN sam_topic ST
ON (STI.topic_id = ST.id)
WHERE (ST.type = 'Topic')
ORDER BY ST.ID

This is all testing by topic_id. If it should be parent_topic_id then change it to this throughout the query.

There is also still the question of what the type field does in selecting records (as I say do you have records other than type='Topic' in the sam_topic table?). Depending upon exactly what this does it may cause problems with the SQL to pick up the duplicates.

|||

Yes there is other records other than "Topic" there is also "ROOT" but I am just worrying about "Topic" and nothing else....This is how I had to change the query it works now partially

SELECT STI.parent_topic_id, ST.description_short, STI.sort_order

FROM (

SELECT parent_topic_id, sort_order

FROM sam_topic_items

GROUP BY parent_topic_id, sort_order

HAVING count(sort_order) > 1

) TS

INNER JOIN sam_topic_items STI

ON (TS.sort_order = STI.sort_order)

INNER JOIN sam_topic ST

ON (STI.topic_id = ST.id)

WHERE (ST.type = 'Topic')

ORDER BY STI.parent_topic_id

but it still does not return only the rows with duplicate sort order for a particular parent_topic_id this query returns

id description_short sort_order

2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Region: 3
2130 Region: 3
2130 Region: 3
2130 Region: 3
2130 Region: 3
2130 Region: 3
2130 Region: 3
2130 Region: 3
2130 Region: 3
2130 Region: 3
2130 Region: 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 1B 4
2131 < 1B 4
2131 < 1B 4
2131 < 1B 4
2131 < 1B 4
2131 < 1B 4
2131 < 1B 4
2131 < 1B 4
2131 < 1B 4
2131 < 1B 4
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1.....thanks 4 the help though!!!

See how it returns all these results for the same thing?...in my table there is only one "<1mm" for the parent_topic_id of 2131 but this returns like 20 of them...I dont know how it did that?

|||

Change your having clause to:

HAVING count(*) >1

|||Yea i did that I still get the same result though?|||

Yeah I get it. I didn't see the whole thing.

You see they have the same id and the same sort_order but diffrent description names.......when i run the above query it returns everything for some reason...I am not clear on how i need to set the criteria to only pull those rows with the same sort_order number for a particular ID? any help?

You need to take the description name out of the group by, otherwise you will count the values with the same id but different descriptions as different rows. To count the number of occurences of a different description for the same id you need to take the description out of your group.

|||Ok here is a better question this query

Select Distinct sti.parent_topic_id, st.Type, sti.sort_order, count(sti.sort_order)

From sam_topic_items sti

Inner Join sam_topic st On sti.topic_id = st.id

Where st.type = 'Topic'

Group By sti.parent_topic_id, sti.sort_order, st.Type

Having count(sti.sort_order) > 1

Order By parent_topic_id

Returns this data exactly

parent_topic_id type sort_order the last column is the number of occurences that the sort_order has.....like if you look at the first row of data.....there are 3 occurences of 0 for parent_topic_id 2334...

2334 TOPIC 0 3
2398 TOPIC 32 2
2399 TOPIC 16 2
2430 TOPIC 22 2
2447 TOPIC 26 2
2447 TOPIC 12 2
2447 TOPIC 15 2
2452 TOPIC 1 3
2505 TOPIC 37 2
2505 TOPIC 52 2
2505 TOPIC 3 2
2507 TOPIC 6 2
2526 TOPIC 32 2
2549 TOPIC 54 2
2549 TOPIC 52 2
2562 TOPIC 0 2
2722 TOPIC 2 2
2880 TOPIC 0 2
2995 TOPIC 0 2
2997 TOPIC 0 2
3000 TOPIC 0 2
3001 TOPIC 0 2
3040 TOPIC 6 2
3152 TOPIC 26 2
3244 TOPIC 0 3
3524 TOPIC 0 2
3605 TOPIC 0 2
3638 TOPIC 0 2
3721 TOPIC 2 2
3723 TOPIC 1 2
4082 TOPIC 4 2
4100 TOPIC 1 3
4181 TOPIC 11 2
4220 TOPIC 0 2
4261 TOPIC 2 2
4262 TOPIC 1 2
4302 TOPIC 3 2
4302 TOPIC 1 2
4302 TOPIC 2 2
4439 TOPIC 0 2
5029 TOPIC 2 3
5032 TOPIC 2 4
5042 TOPIC 2 6
5042 TOPIC 11 2
5071 TOPIC 2 3
5297 TOPIC 2 2
5297 TOPIC 3 2
5297 TOPIC 1 2
5368 TOPIC 52 2
5368 TOPIC 3 2
5368 TOPIC 36 2
5368 TOPIC 48 2
5368 TOPIC 37 2
5368 TOPIC 54 2
5849 TOPIC 45 2
5882 TOPIC 16 2
5882 TOPIC 42 2
5882 TOPIC 54 2
5882 TOPIC 11 2
5882 TOPIC 55 3
5882 TOPIC 60 2
5882 TOPIC 40 2
6032 TOPIC 0 3
6137 TOPIC 0 2
8897 TOPIC 3 2
9461 TOPIC 2 2
9461 TOPIC 1 2
9598 TOPIC 11 2
9608 TOPIC 54 2
10345 TOPIC 0 2
10366 TOPIC 2 2
10627 TOPIC 17 2
11992 TOPIC 42 2
12081 TOPIC 4 2
12083 TOPIC 3 3
12298 TOPIC 20 2
12701 TOPIC 0 2
12716 TOPIC 9 4
12717 TOPIC 4 2
12750 TOPIC 6 2
12755 TOPIC 3 2
12760 TOPIC 1 2
12760 TOPIC 3 2
12760 TOPIC 4 2
12760 TOPIC 2 2
12926 TOPIC 5 3
12990 TOPIC 8 6
12998 TOPIC 9 4
12998 TOPIC 6 2
13001 TOPIC 2 2
13069 TOPIC 5 2
13069 TOPIC 1 2
13132 TOPIC 8 2
13134 TOPIC 4 2
13349 TOPIC 5 3
13429 TOPIC 4 2
13429 TOPIC 19 3
13429 TOPIC 1 2
13429 TOPIC 3 2
13429 TOPIC 6 2

Now using the above query how would i write a sub query to use the data return in this result to show some other data in the same result set?

|||

Like what? I thought thats what you wanted to show?

Your result set is going to be grouped.

It will only have a single row for each group.

You can do simple tricks like putting an aggregate function like MIN function to show things like the description but you will only show a single description for the grouped row.

What is it exactly that you are trying to show?

Have you considered an sp rather than a single query?

ALSO, please not that you DO NOT need st.Type in the grouping or the field list since your where criteria clearly states that it has to be equal to 'TOPIC'.

|||

thanks liz.....

If you look at the results for the query....and look at the id of 2334 it has 3 instances of 0 in that id....I want to be able to show

2334 TheNAme 0

2334 The OtherName 0

2334 The OtherName 0

I want to show the id the description and the sort order.....Only for those duplicate sort orders and thats it.....I could write a vb or C# app to do it....but i need to learn sql, haha as you can see i dont know it well!!! thanks!!

|||

It is real ugly but, if you are using 2 values you need to convert them to a char string and use them in the where clause together in the outer query...

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 convert(char(20), sti.parent_topic_id) + convert(char(20), sti.sort_order) IN
(
Select convert(char(20), sti.parent_topic_id) + convert(char(20), sti.sort_order)
From sam_topic_items sti
Inner Join sam_topic st On sti.topic_id = st.id
Where st.type = 'Topic'
Group By sti.parent_topic_id, sti.sort_order, st.Type
Having count(sti.sort_order) > 1
Order By parent_topic_id
)

I don't know if this will execute properly because I don't have the right tables to test it but it is the general idea...

|||

First off please explain to me what the convert method did? I need to know what that did to the query to make it act like so....

BUT you are a WIZ!!!!!!!!! unless I am just that mentally slow THANK!!! YOU!!!!THANK!!! YOU!!!!THANK!!! YOU!!!!THANK!!! YOU!!!!THANK!!! YOU!!!!THANK!!! YOU!!!!......