how to select a single row based on an aggregate SQL function result?
-
Hello, let's have the following simple example. An SQL table consists of three columns: RecordId, GenerationId, Fitness. RecordId is a unique key. GenerationId and Fitness columns contain values that might and do repeat themselves over many rows. Now how to do a simple select of the unique RecordId which has the highest Fitness, grouped by GenerationId? Obviously, SELECT max(Fitness) FROM myTable GROUP BY GenerationId returns the highest Fitness for each GenerationId, but how to get the particular row which has this highest Fitness? Fitness values might repeat themselves for the same GenerationId values, so an aggregate function also has to be used to select a distinct RecordId for a particular GenerationId. It doesn't matter which RecordId is selected as long as it has the highest Fitness for the particular GenerationId. Thank you very much for any ideas! Michal
-
Hello, let's have the following simple example. An SQL table consists of three columns: RecordId, GenerationId, Fitness. RecordId is a unique key. GenerationId and Fitness columns contain values that might and do repeat themselves over many rows. Now how to do a simple select of the unique RecordId which has the highest Fitness, grouped by GenerationId? Obviously, SELECT max(Fitness) FROM myTable GROUP BY GenerationId returns the highest Fitness for each GenerationId, but how to get the particular row which has this highest Fitness? Fitness values might repeat themselves for the same GenerationId values, so an aggregate function also has to be used to select a distinct RecordId for a particular GenerationId. It doesn't matter which RecordId is selected as long as it has the highest Fitness for the particular GenerationId. Thank you very much for any ideas! Michal
the result should look something like this: GenerationId, Fitness, RecordId 0,312.53,12 1,365.42,18 2,412.56,28 3,418.26,34 4,526.79,41 5,528.,48 ...
-
the result should look something like this: GenerationId, Fitness, RecordId 0,312.53,12 1,365.42,18 2,412.56,28 3,418.26,34 4,526.79,41 5,528.,48 ...
-
Do you have some sample data for the table that would be used to get the result you're looking for? Scott
yes, let's use the following sample data. source table: RecordId GenerationId Fitness 0 0 150.5 1 0 148.2 2 0 172.5 3 1 183.2 4 1 189.8 5 1 183.2 6 1 215.1 7 1 221.3 8 2 208.4 9 2 232.1 10 3 225.1 11 3 236.4 12 3 231.5 13 3 236.4 14 4 236.4 15 4 245.1 16 5 251.9 17 5 250.4 18 6 251.9 19 6 262.4 based on how one aggregates multiple rows with the same Fitness for the same GenerationId, one of the possible outcomes should look like the following. here I used max(RecordId), but it doesn't really matter: GenerationId maxFitness RecordId 0 172.5 2 1 221.3 7 2 232.1 9 3 236.4 13 4 245.1 15 5 251.9 16 6 262.4 19 I'm able to achieve this result with joining the source table to itself, but I hope there has to be a more elegant way on how to do this without breaking one's neck with multiple lines of SQL code. thanks for any help and ideas! Michal
-
yes, let's use the following sample data. source table: RecordId GenerationId Fitness 0 0 150.5 1 0 148.2 2 0 172.5 3 1 183.2 4 1 189.8 5 1 183.2 6 1 215.1 7 1 221.3 8 2 208.4 9 2 232.1 10 3 225.1 11 3 236.4 12 3 231.5 13 3 236.4 14 4 236.4 15 4 245.1 16 5 251.9 17 5 250.4 18 6 251.9 19 6 262.4 based on how one aggregates multiple rows with the same Fitness for the same GenerationId, one of the possible outcomes should look like the following. here I used max(RecordId), but it doesn't really matter: GenerationId maxFitness RecordId 0 172.5 2 1 221.3 7 2 232.1 9 3 236.4 13 4 245.1 15 5 251.9 16 6 262.4 19 I'm able to achieve this result with joining the source table to itself, but I hope there has to be a more elegant way on how to do this without breaking one's neck with multiple lines of SQL code. thanks for any help and ideas! Michal
SELECT g.GenerationId,
d.Fitness,
d.RecordId
FROM
(
SELECT DISTINCT GenerationId
FROM [Source]
) AS g
CROSS APPLY
(
SELECT TOP 1 s.RecordId, s.Fitness
FROM [Source] AS s
WHERE s.GenerationId = g.GenerationId
ORDER BY s.Fitness DESC
) AS d
ORDER BY g.GenerationId ASC -
yes, let's use the following sample data. source table: RecordId GenerationId Fitness 0 0 150.5 1 0 148.2 2 0 172.5 3 1 183.2 4 1 189.8 5 1 183.2 6 1 215.1 7 1 221.3 8 2 208.4 9 2 232.1 10 3 225.1 11 3 236.4 12 3 231.5 13 3 236.4 14 4 236.4 15 4 245.1 16 5 251.9 17 5 250.4 18 6 251.9 19 6 262.4 based on how one aggregates multiple rows with the same Fitness for the same GenerationId, one of the possible outcomes should look like the following. here I used max(RecordId), but it doesn't really matter: GenerationId maxFitness RecordId 0 172.5 2 1 221.3 7 2 232.1 9 3 236.4 13 4 245.1 15 5 251.9 16 6 262.4 19 I'm able to achieve this result with joining the source table to itself, but I hope there has to be a more elegant way on how to do this without breaking one's neck with multiple lines of SQL code. thanks for any help and ideas! Michal
Personally, I don't see a problem with joining to the source table itself, or an inline view built off of the source table, so I came up with this which returns your desired result: SELECT cp.generationid, cp.fitness, MAX(cp.recordid) FROM cp, (SELECT generationid, MAX(fitness) maxfitness FROM cp GROUP BY generationid) view1 WHERE cp.fitness = view1.maxfitness AND cp.generationid = view1.generationid GROUP BY cp.generationid, cp.fitness ORDER BY 1, 2, 3 view1 is an inline view off of the original table that I'm joining to. I saw i.j.russell's response, but I'm not familiar with it (I'll need to read up on it). Scott
-
Personally, I don't see a problem with joining to the source table itself, or an inline view built off of the source table, so I came up with this which returns your desired result: SELECT cp.generationid, cp.fitness, MAX(cp.recordid) FROM cp, (SELECT generationid, MAX(fitness) maxfitness FROM cp GROUP BY generationid) view1 WHERE cp.fitness = view1.maxfitness AND cp.generationid = view1.generationid GROUP BY cp.generationid, cp.fitness ORDER BY 1, 2, 3 view1 is an inline view off of the original table that I'm joining to. I saw i.j.russell's response, but I'm not familiar with it (I'll need to read up on it). Scott
Thank you very much for all your replies! I used my original code with joining the table to itself at last. I was looking for some really simple way on how to handle this, but it looks there's no free lunch in T-SQL :) Thanks again for all your valuable input! Michal