This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.
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.
...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