Solved

# Selecting distinct records

Posted on 2010-08-28
640 Views
I am writing a select query from a table TEST having three colums A,B,C in which i want to select distinct records on the basis of both the colums A and B.

If i give:

select distinct A,B,C from test;

it gives only the distinct records on the basis of column A. I tried this also:

select distinct A,B,C from test group by A,B;

but not working.  Kindly suggest.
0
Question by:mohitgyl
• 2
• 2
• 2
• +1

LVL 57

Expert Comment

ID: 33549286
Try using:

select A,B,MAX(C) as C
from test
group by A,B;

or

select A,B,MAX(C) as C
from test
group by A,B;
0

Author Comment

ID: 33549381
I dont understand why did u suggest using max(C), i think that will select max value for column C but my problem is different.

I want to select distinct records on the basis of both column A and B but not column C.
0

LVL 57

Expert Comment

ID: 33549393
Then try this:

SELECT A, B, C
FROM (
select A,B,C, ROW_NUMBER() OVER ( Partition By A, B order by C) as rnum
from test ) temp
WHERE rnum = 1
0

LVL 45

Expert Comment

ID: 33549479
Hi mohitgyl,

Can you explain a bit about what you see in the original query?

SELECT distinct a, b, c FROM mytable;

That query will filter out all rows where a,b,c are ALL exactly like the values of another row.  (It will be selected only once.)  I does not filter only on column "a".

Kent
0

LVL 27

Expert Comment

ID: 33580564
...it gives only the distinct records on the basis of column A.

I am not clear at all what the problem is. It sounds as if you are trying to get DISTINCT A, DISTINCT B and DISTINCT C, all somehow included in a single result set.

But that doesn't make sense to me.

Can you supply a few example rows from a table and show what result you expect to see from those rows?

Tom
0

LVL 27

Accepted Solution

tliotta earned 500 total points
ID: 33599260
I mistakenly included "DISTINCT C" in my previous comment. But it's still not clear about the meaning two DISTINCT columns while a third column is not DISTINCT.

...distinct records on the basis of both column A and B but not column C.

You also said "why did u suggest using max(C)". If you don't use max(C) or some similar function, how else while the query know which C value to retrieve?

A   B   C
--  --  ---
a1 b1 c1
a1 b1 c2
a2 b2 c3
a2 b2 c4
a3 b4 c5
a3 b5 c6
a4 b4 c7

DISTINCT values for A are (a1, a2, a3, a4). DISTINCT values for B are (b1, b2, b4, b5).

So, DISTINCT A CONCAT B values are (a1b1, a2b2, a3b4, a3b5, a4b4).

For the (a2b2) value, which value for C should be in the result set -- c3 or c4? There can be only one because the (a2b2) value must be DISTINCT. You need some way to choose which value of C should be picked. The max(C) function will make the choice between (c3) and (c4)

Tom
0

Author Closing Comment

ID: 33767381
Satisfied
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…