How to get distinct values in 2 Column in a SQL Table

Question

5.00/5 (1 vote)

See more:

SQL-Server-2008R2

Table 1

  APPID  | CERTID       | 	CERTNAME
--------------------------------
11111    |	1	|	ABCD	
22222    |	2	|	EFGH
33333    |	3	|	IJKL
11111    |	4	|	XYZ
44444    |	5	|	JHJ
22222    |	6	|	KJHK
11111    |	7	|	TUHJ

Note:- here cert is is PK and its not repeatable where as App Id is repeatable columns values

In this table How do I get distinct AppId and corresponding certId ,

This is my query , here still I am getting duplicate AppId's

SQL

Select distinct(APPID),CERTID from Table1

Please Please Can any one help me.

[edit]Code block added - OriginalGriff[/edit]

Posted 28-Aug-14 0:34am

shankar01021981

Updated 28-Aug-14 0:37am

OriginalGriff

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2014-08-28T00:46:00

Solution 2

Try this:

SQL

SELECT APPID, CERTID
FROM (SELECT APPID, CERTID,
             ROW_NUMBER() OVER (PARTITION BY APPID ORDER BY APPID) AS RowNumber
      FROM   Table1) AS a
WHERE   a.RowNumber = 1

Posted 28-Aug-14 0:46am

OriginalGriff

Comments

Sanchayeeta 28-Aug-14 7:22am

I didn't see your answer before posting my answer Otherwise i wouldn't. Its working Perfectly.

Sanchayeeta · Answer 2 · 2014-08-28T01:18:00

Solution 3

For each distinct APPID there are Different CERTID, like for APPID 11111 there are three CERTID 1,4,7. You have to pic one of them otherwise APPID will repeat.

As per your requirement I think this will work for you.

SQL

select APPID, MAX(CERTID) from Table1
group by APPID

Posted 28-Aug-14 1:18am

Sanchayeeta

Thilina Chandima · Answer 3 · 2014-08-28T00:43:00

Solution 1

SQL

Select distinct APPID,CERTID 
from Table1
group by APPID,CERTID

Posted 28-Aug-14 0:43am

Thilina Chandima

Updated 28-Aug-14 0:44am

v2

Comments

OriginalGriff 28-Aug-14 6:51am

Reason for my vote of one: did you try that at all? Because it generates exactly the same results as
SELECT APPID,CERTID FROM Table1
given that CERTID is the primary key and can't be contain duplicated values.

Please read the question before answering in future...