SQL query for distinct values

Question

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

Well, IPR123 1576 2345 1.000 and why not IPR235 3459 4987 1.000? Define additional criteria to decide which row to select. — zerkms
– zerkms, Commented Nov 13, 2010 at 7:03
any row with one distinct confidence value is taken .There is no particular criteria. — manasa n
– manasa n, Commented Nov 13, 2010 at 7:04

Adriaan Stander · Accepted Answer · 2010-11-13 07:10:50Z

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)

Andrew · Accepted Answer · 2010-11-13 16:19:09Z

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.

I thought of that after I posted and left the computer for the night. If INTERPRO is not unique per CONFIDENCE, then this wouldn't work, so time for an edit...

LIX · Accepted Answer · 2010-11-13 09:54:38Z

-1

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

edited Nov 13, 2010 at 9:54

answered Nov 13, 2010 at 7:12

LIX

1,5496 gold badges23 silver badges33 bronze badges

2 Comments

zerkms Over a year ago

This query will give non-consistent data

LIX Over a year ago

as user489678 said : any row with one distinct confidence value is taken .There is no particular criteria.

Collectives™ on Stack Overflow

SQL query for distinct values

3 Answers 3

Comments

2 Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

2 Comments

Related