Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
Selecting distinct records
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.
Who is Participating?
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.
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
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
...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
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
All Courses
From novice to tech pro — start learning today.
-
Course of the Month6 days, 9 hours left to enrollMonitoring and Operating a Private Cloud with System Center 2012 R2 273 lessons
...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