CODEWITHSUNDEEP
mysqlsqlmin
aledj2
aledj2

Reputation: 53

SQL: multiple minimum values

I am using mySQL and database master and I am having trouble getting the minimum value of one column for every different value of a second column.

I have a table:

Gene_exon (varchar), Probe_ID (Varchar) PRIMARY KEY, DISTANCE(INT), START(INT), STOP(INT).

There are multiple entries for each exon with the distance between this exon and different probes.

For each exon I'd like to find the min distance, and the corresponding probe_ID,start and stop.

I can get the correct result when I specify an exon but I want the correct result for every exon with one query!

any ideas?

thanks in advance

edit: Bingo! by using group by and the below code I have managed to find what I wanted. the minimum distance per exon, which has all the correct information, :

SELECT gene_exon,probe_id, distance_before_EXON_START, start, stop
FROM probe_location a
WHERE distance_before_EXON_START>0 and NOT EXISTS (SELECT * FROM probe_location B
WHERE distance_before_EXON_START>0 and A.distance_before_EXON_START>b.distance_before_EXON_START AND a.gene_exon = b.gene_exon)
GROUP BY gene_exon, probe_id, start, stop;

Upvotes: 2

Views: 4229

Answers (1)

user804018
user804018

Reputation:

Use GROUP BY. The trick is that every column except the one you are getting the min of is in the GROUP BY:

SELECT a,b,c, min(d)
FROM myTable
GROUP BY a,b,c
ORDER BY a,b,c

Upvotes: 3

Related Questions