I have a table with 14 columns. I'm trying to select the rows (and sort them together) where 2 of those columns match.

These aren't actual duplicates, because the entire row isn't going to match; just col3 and col5. And, I need to return all of the found rows (not de-duped), but with the ones with duplicate col3 and col5 sorted together.

Thoughts? TIA,

Jason

On 11/18/2011 5:23 AM, Jason C wrote:
First thought is if you have the same data in multiple columns (even if
the actual values may differ) your database is not normalized.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

On 2011-11-18 11:23, Jason C wrote:
Not sure what you mean by all of the found rows, do you mean all of the
columns?

select c1,c2, ...,c14
from t
where c3 = c5
order by case when c3 = c5 then 0 else 1 end

if you want rows where c3 <> c5 as well, remove the where clause.


If the duplicates can occur in any column you can use a pattern like:

select c1,c2, ...,c14
from t
where c1 in (c2, c3, ... , c14)
or c2 in (c3, c4, ..., c14)
or ...
or c13 in (c14)


the same pattern can be used in a case stmt in the order by clause


/Lennart

On Friday, November 18, 2011 9:04:48 AM UTC-5, Jerry Stuckle wrote:
No, I just didn't give the whole back story.

I'm sharing some data with another website who has some similar content. I merged out content in a temporary table, and now I'm trying to go through it and find the data that may match.

To some degree, this can be automated, but for the most part it's going to have to be manual. For example, col3 is a Street Address, which should be the same in my data and his. But, of course, I might have "St." where he has"Street", or "North" where he has "N.", so it's not going to always be a perfect match.

Which, of course, is why I'm trying to find rows where col3 and col5 match,then sort them together.

On Friday, November 18, 2011 11:43:27 AM UTC-5, Lennart Jonsson wrote:
Thanks for the info, Lennart. But no, I'm trying to select rows where data matches another row.

It's sort of like:

Row A:
col1: Mike
col2: Smith
col3: 123 Main Street
col4: Apt. B
col5: Los Angeles
col6: CA

Row B:
col1: Michael
col2: Smith, Jr
col3: 123 Main Street
col4: Apartment B
col5: Los Angeles
col6: California

So, what I'm trying to do is select all rows where col3 and col5 have more than one row with the same content, and then sort them together.

I can't just sort by col3, because with 28,000 rows, I could have a lot of rows with 123 Main Street, but in different cities. I need them to be grouped together when the col3 and col5 match.

On 11/18/2011 4:16 PM, Jason C wrote:
OK, I'm not sure why you merged tables when comparing two tables would
be just as easy. But anyway, assuming col3 and col5 both contain the
street address, you can do something like

SELECT col1, col2, col3 ....
FROM temptable
WHERE col3 = col5

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

On 11/18/2011 4:22 PM, Jason C wrote:
Ah, that's a little better explanation. Forget my previous example.
This isn't too hard - the trick being you need to do a self-join on the
table:

SELECT a.col1, a.col2, a.col3, ...
FROM temptable a
JOIN temptable b
ON a.col3 = b.col3 and a.col5 = b.col5;

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

On 18-11-2011 22:22, Jason C wrote:
SELECT ...... GROUP BY col5, col3

--
Luuk

On 2011-11-18 22:22, Jason C wrote:
[...]
Ah, ok I see (I think :-)

select t1.c1, t1.c2, ..., t1.c14
from t as t1
join (
select c3, c5
from t
group by c3, c5
having count(1) > 1
) as t2
on (t1.c1, t1.c3) = (t2.c1, t2.c3);

/Lennart

On 2011-11-18 23:55, Lennart Jonsson wrote:
[...]
-- oops

- on (t1.c1, t1.c3) = (t2.c1, t2.c3);
+ on (t1.c3, t1.c5) = (t2.c3, t2.c5);

/Lennart