Min(). More than 1 result

[miktilo]

Hello there,

This is probably a simple task but I cant seem to get it. I have tried many different things with no success.

 

If I have a query that gives the following result:

 

+-----------+------------+--------------------+----------+

| name      | position | clusterNumber |        ids |

+-----------+------------+--------------------+----------+

| TYR      |            75 |                      60 |      454 |

| TYR      |            76 |                      60 |      421 |

| GLY      |              4 |                      62 |      801 |

| GLY      |            10 |                    62 |        697 |

| ALA        |            11 |                    62 |        711 |

| GLU      |            11 |                    62 |          10 |

| ASN      |            14 |                  228 |        108 |

| SER      |            14 |                  228 |        106 |

| ASP      |            40 |                  228 |        298 |

| SER      |            40 |                  228 |        297 |

 

for this query I use the function min() and group my columns by clusterNumber and ids. If I group only by clusterNumber, then min() returns only only one row. The position is right, but I need also the ids.

 

+-----------+------------+--------------------+----------+

| name      | position | clusterNumber |        ids |

+-----------+------------+--------------------+----------+

| TYR      |            75 |                      60 |      454 |

| GLY      |              4 |                      62 |      801 |

| ASN      |            14 |                  228 |        108 |

 

 

Is there any way to select only the ids and names of the minimum position for each clusterNumber ?

 

+-----------+------------+--------------------+----------+

| name      | position | clusterNumber |        ids |

+-----------+------------+--------------------+----------+

| TYR      |            75 |                      60 |      454 |

| GLY      |              4 |                      62 |      801 |

| ASN      |            14 |                  228 |        108 |

| SER      |            14 |                  228 |        106 |

 

 

I have tried with subqueries and it works. However, for that I need to access the same tables twice (due to conditions that the position must fullfill). Is it possible to force min() to return more than one result ? Or to use variables ??

 

Thanks.

[kickstart]

Hi

 

Not really. While you can put the extra columns in and it might well work the results are not guaranteed and it is a bit of a bodge.

 

The basic mix of fields you want would be got with something like this

 

SELECT a.*
FROM SomeTable a
INNER JOIN (SELECT ClusterNumber, MIN(ids) AS MinIds FROM SomeTable GROUP BY ClusterNumber) b
ON a.ClusterNumber = b.ClusterNumber AND a.ids = b.MinIds

 

However this gets you a single record for each cluster number even if several have the same position

 

Expanding that a bit

 

SELECT a.*
FROM SomeTable a
INNER JOIN (SELECT x.ClusterNumber, x.Position
FROM SomeTable x
INNER JOIN (SELECT ClusterNumber, MIN(ids) AS MinIds FROM SomeTable GROUP BY ClusterNumber) z
ON x.ClusterNumber = z.ClusterNumber AND x.ids = z.MinIds
) b
ON a.ClusterNumber = b.ClusterNumber AND a.Position = b.Position

 

Bit messy but something like that should work (not tested so probably some typos).

 

All the best

 

Keith

[miktilo]

Hey, thanks for the answer...

I am going to try it now ..