Select uniques, and one of the doubles

Question

I have a table with columns A, B and C. Column A might have duplicates.

I need a query that will get me a resultset with unique values in column A, and I don't care which possible duplicate it takes.

I don't know anything beforehand about the rest of the data.

An example might be:

A    B    C
1    8    8
1    7    7
2    10   10

In this case I'd want to select:

A    B    C
1    x    x
2    10   10

x = It doesn't matter which value it would pick.

Kind regards,

Matthias Vance

Edit

I thought I found my solution with:

SELECT * FROM (
   SELECT * FROM test GROUP BY a
) table_test;

But that wasn't working after all.

This will result in:

[Microsoft][ODBC Excel Driver] Cannot group on fields selected with '*'

Answer 1

Wouldn't this simple query work:

SELECT A, MIN(B), MIN(C) FROM test GROUP BY A

It groups by A and just selects the minimum values of B and C in the rows of A. The values of B and C might come from different rows, e.g.

A  B  C
1  2  3
1  4  1 

would return

A  B  C
1  2  1 

Answer 2

This works in SQL Server 2008, which illustrates the concept. You need a unique column.

declare @temp as table (
id int identity(1,1),
a int,
b int, 
c int)

insert into @temp
    select 1 as A, 8 as B, 8 as C
    union
    select 1, 7, 7
    union 
    select 2, 10, 10

select a, b, c from @temp
where id in (select MAX(id) from @temp
group by a)

Seeing that you're using Excel, I'd use the same principle. Add another column to the spreadsheet and make sure it is unique. Use that column as your ID-column.

Answer 3

Select A
    , Max(b) //Since You don't care about the Value
    , Max(c) //Since You don't care about the Value
From table t
Group By A

Answer 4

I know this is a dirty way, but will work this case.

Pseudo code:

create table #tmpStaging with primary key as col( A )

for each row in the flatFile/excel/whatever begin begin try insert into #tmpstaging end try

begin catch --do nothing end catch end

select * from #tmpstaging will give you rows without dups

Answer 5

Try this,

SELECT UT.[A],
(SELECT TOP 1 B FROM [YourTable] WHERE [YourTable].A= UT.A) AS B,
(SELECT TOP 1 C FROM [YourTable] WHERE [YourTable].A= UT.A) AS C  FROM [YourTable] AS UT GROUP BY UT.[A]

I didn't tried it yet... who knows :)

Answer 6

Try this:

select A, B, C
from test x
where not exists (select *
                  from test y
                  where y.A = x.A
                        and (y.B < x.B or (y.B = x.B and y.C < x.C))
order by A

But since it contains correlated subquery it might be slow. (OTOH it is at least theoretically possible for database engine to optimize it into something I show below.)

---

What about something outside SQL? What are you going to do with result?

If you are going to process it with some program, why not just get:

select A, B, C from test order by A, B, C

and then do something like:

prev_a = None
for a, b, c in get_query_result():
    if a != prev_a:
        prev_a = a
        yield (a, b, c)

in your application?

I don't know PHP but I guess it would be something like this:

$query = "SELECT a,b,c FROM test ORDER BY a,b,c";
$result = odbc_exec($connect, $query);
$prev_a = NULL;  # I don't know what you would normally use here in PHP
while (odbc_fetch_row($result)) {
  $a = odbc_result($result, 1);
  if (is_null($prev_a) or $a != $prev_a) { 
    $b = odbc_result($result, 2);
    $c = odbc_result($result, 3);
    print("A = $a, B = $b, C = $c\n");
    $prev_a = $a;
  }
}