Selecting DISTINCT values across 2 column from table

[tamilsweet]

Hi all,
I have 2 cols.

col A col B
3377 1860
3779 1860
3772 1860
3753 1860
3377 1861
3772 1861
3753 1861
3779 1864
3779 1865
3772 1865
3779 1867
3772 1868
3779 1869
3772 1869

I want to fetch max. unique combination something like.
3377 1860
3753 1861
3779 1864
3772 1868

How do i do that?

[abduraooft]

3377 1860
3753 1861
3779 1864
3772 1868 I don't get the criteria of your selection, though try
SELECT col_A, max(col_B) from table1 group by col_A

[tamilsweet]

Thanks for your reply.
Sorry for not being clear.

Let me try to give another example.

ColA | ColB
1 | 1
1 | 2
2 | 1
3 | 1
3 | 2
3 | 3
4 | 2

Possible distinct combinations. Both the columns shouldn't be repeated.
1 | 2
2 | 1
3 | 3

or

1 | 1
3 | 3
4 | 2

or

1 | 1
3 | 2

etc.

How ever I want to find the one with max. result rows.

Can be done this way.
Step 1: sort by Count Of Group By ColB
3 | 3
1 | 1
2 | 1
3 | 1
1 | 2
3 | 2
4 | 2

Then select distinct combination from here.
3 | 3
1 | 1
4 | 2

Sorry if I'm not clear again!

[tamilsweet]

Let me try to put the question in simpler way

A | B
1 | 1
1 | 2
1 | 3
2 | 1
2 | 2
3 | 1

Picking up unique A & unique B (not = unique(A&B))

1 | 1
2 | 2
(2 rows)

another combination

1 | 3
2 | 2
3 | 1
(3 rows)

another combination

1 | 2
2 | 1
(2 rows)

another combination
1 | 2
3 | 1
(2 rows)

I want to get the max rows. i.e. the combination with 3 rows in this example.

[Old Pedant]

Let me try this in my own words:

From a group of (A,B) pairs, find a maximal set of pairs where neither the value of A nor the value of B is duplicated any place in the set.

Does that express it?? I used "A maximal set" instead of "THE maximal set" on the assumption that you don't care which is found if there is more than one such set with the same number of members.

I'm not sure you can do this with a SQL query. *POSSIBLY* you can do it with a Stored Procedure. But I'll think about it. In the meantime, confirm/deny my wording, please.

[tamilsweet]

Hi, Thanks a lot.
Yes, you put my requirement into simple and short words.

I just need a maximal set and not bothered about which set it is.

[Old Pedant]

There are several ways to test if a given set fulfills your "no matches" requirement.

Example:

SELECT IF ( COUNT(DISTINCT A) = COUNT(DISTINCT B) AND COUNT(DISTINCT A) = COUNT(*), 'Yes', 'No' ) AS isValid
FROM set

But the trick is finding the sets. I just don't see a way to do that in SQL, alone.

It's not hard, procedurally, though if your original table is large it sure could take a long time!

I can't see any algorithm that is better than order N-squared.