selecting only unique rows

Anon

I have a table with four columns, each having a unique id. There are some duplicate records e.g.

col1 | col 2 | col 3 | col 4
1 | hello | there | jack
2 | how | are | you
3 | I | am | fine
4 | hello | there | jack
5 | how | are | you

I want a sql to display only record 3, or better still, just display 3. So, it ignores all rows with duplicates.

mikeq

try

select count(*), col1, col2, col3, col4
from mytable
group by col1, col2, col3, col4
having count = 1;

Anon

That would not work. col1 has unique elements, so your query still returns all the data in the table. Any thoughts?

Anon

Let me rephrase the question. I have a table with four columns, each having a unique id e.g. test

col1 | col2 | col3 | col4
1 | hello | there | jack
2 | how | are | you
3 | I | am | fine
4 | hello | there | jack
5 | how | are | you

col2, col3, col4 when grouped, have duplicate rows. How do I select from test table record 3 so my query result displays: 3 I am fine, or better still, just 3.

[deleted]

The leave col1 out of the select and the group by.

But if you want to return only col1, you're stuck with a temp-table query.

do something like

select count(*), col2, col3, col4
from mytable
group by col2, col3, col4
having count = 1;

and insert it's output into a temporary table (could also be PHP, but temp tables are probably faster)

Then join the content of the temp table with the real table based on col2, col3 and col4, and get the related col1.

messy but it should work.

A forum, a FAQ, email notification, what else do you need?