Solved

Selecting distinct records

Posted on 2010-08-28
7
638 Views
Last Modified: 2012-05-10
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
Comment
Question by:mohitgyl
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
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

by:mohitgyl
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

by:Raja Jegan R
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 45

Expert Comment

by:Kdo
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

by:tliotta
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

by:
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)

Show a sample of data and show your expected result, please.

Tom
0
 

Author Closing Comment

by:mohitgyl
ID: 33767381
Satisfied
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now