CODEWITHSUNDEEP
sqlsql-servert-sqldistinct
manasa n
manasa n

Reputation: 29

SQL query for distinct values

I have a table as below

INTERPRO    GOTERM1  GOTERM2   CONFIDENCE

IPR123      1576     2345       1.000
IPR235      3459     4987       1.000
IPR356      9987     5567       1.000
IPR568      3345     3213       0.88
IPR342      7860     7654       0.88

Now I want any of the record with distinct confidence values only like below

IPR123   1576    2345   1.000
IPR342   7860    7654    0.88

Upvotes: 1

Views: 281

Answers (3)

Andrew
Andrew

Reputation: 14457

SELECT t.INTERPRO, t.GOTERM1, t.GOTERM2, t.CONFIDENCE
FROM   tbl t
WHERE  t.INTERPRO = (SELECT MIN(t2.INTERPRO)
                     FROM   tbl t2
                     WHERE  t.CONFIDENCE = t2.CONFIDENCE)

Edit:

If INTERPRO is non-unique-per-CONFIDENCE:

SELECT t2.INTERPRO, t2.GOTERM1, t2.GOTERM2, t1.CONFIDENCE
FROM   (SELECT DISTINCT t3.CONFIDENCE FROM tbl t3) t1,
       (SELECT TOP 1 t4.INTERPRO, t4.GOTERM1, t4.GOTERM2
        FROM   tbl t4
        WHERE  t4.CONFIDENCE = t1.CONFIDENCE) t2

should work, though I don't have a SQL Server instance handy at the moment to double-check.

Upvotes: 0

LIX
LIX

Reputation: 1529

if INTERPRO is unique 

SELECT * FROM TABLE WHERE INTERPRO in 
(
SELECT MIN(INTERPRO) FROM TABLE
GROUP BY CONFIDENCE
)

you can replace MIN() with anything you like

Upvotes: -1

Adriaan Stander
Adriaan Stander

Reputation: 166546

Using Sql Server 2005+ you can try something like

;WITH Vals AS(
        SELECT  *,
                ROW_NUMBER() OVER (PARTITION BY CONFIDENCE ORDER BY INTERPRO ) RowID
        FROM    YourTable
)
SELECT  *
FROM    Vals
WHERE   RowID = 1

ROW_NUMBER (Transact-SQL)

Upvotes: 2

Related Questions