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
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
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.
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
IPR123 1576 2345 1.000and why notIPR235 3459 4987 1.000? Define additional criteria to decide which row to select.