SQL: select multiple columns based on multiple groups of minimum values?

Question

The following query gives me a single row because b.id is pinned. I would like a query which I can give a group of ids and get the minimum valued row for each of them.

The effect I want is as if I wrapped this query in a loop over a collection of ids and executed the query with each id as b.id = value but that will be (tens of?) thousands of queries.


select top 1 a.id, b.id, a.time_point, b.time_point
from orientation_momentum a, orientation_momentum b
where a.id = '00820001001' and b.id = '00825001001'
order by calculatedValue() asc

This is on sql-server but I would prefer a portable solution if it's possible.

iruvar · Accepted Answer · 2012-10-02 11:51:22Z

1

SQL Server ranking function should do the trick.

select * from (
select a.id, b.id, a.time_point, b.time_point, 
rank() over (partition by a.id, b.id
order by calculatedValue() asc) ranker
from orientation_momentum a, orientation_momentum b
where a.id = '00820001001' and b.id between 10 and 20
) Z where ranker = 1

edited Oct 2, 2012 at 11:51

answered Oct 2, 2012 at 1:21

iruvar

23.5k7 gold badges58 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

asm Over a year ago

Thanks, that's exactly what I was looking for. One note, the columns need to be renamed or there will be errors like column 'id' specified multiple times for Z.

Collectives™ on Stack Overflow

SQL: select multiple columns based on multiple groups of minimum values?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related