Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments
Course Of The Month
9 days, 13 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Selecting distinct records
Posted on 2010-08-28
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.
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.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2
2-
2
- +1
7 Comments
Active today
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;
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;
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.
I want to select distinct records on the basis of both column A and B but not column C.
Active today
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
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
Active today
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
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
Active today
...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
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
Active today
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
...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
Featured Post
Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence)
A growing number of database queries lend themselves to recursive solutions. It's not always easy to spot when recursion is called for, especially for people una…
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…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar.
Int…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers.
Hopes this gives you ideas on visualizing your data in new ways ~
Create a calculated field in a query:
…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month9 days, 13 hours left to enroll
624 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.