Re: Complex query

You could union the two and then use it as a subquery, getting a min
of value:

select A.id, min(A.value)
from (
select id, value from table
union
select id, value from table2 ) A
group by A.ID

On Mar 3, 12:47 am, usawarga...@xxxxxxxxx wrote:
My dataset looks like this:
2 groups (G1 and G2) of 4 items each

Group G1 contains
id value
A1 9
A2 7
B1 3
B2 2

Group G2 contains
id value
A3 9
A4 7
B2 1
B3 0

I would like a query that returns the item(s) with the lowest value
with the restriction that if taht item is in multiple groups it must
have teh lowest value in each group (or be tied for the lowest)

B2 in group 1 has a value of 2 (the lowest in that group), but its
also in group 2 and has a value (1) that is not the lowest in that
group (even though its lower than in group 1)

The above is a simplification, as there will be several groups, each
with up to 10 items.
There are perhaps 10 groups and 50 items max.

How can I write this query other tahn through a set of while
statements and temp tables taht are iteratively built up?

Thank you for your help

.

Relevant Pages

  • Re: Multiple subqueries in a union query
    ... Create a query that uses TableA, TableC, and QueryD. ... If that's the right records, switch to SQL View, and UNION the similar ... Access 2003 that works as long as a second subquery is not added to the ...
    (microsoft.public.access.queries)
  • Re: Multi-Level GROUP By Clause is not allowed in subquery
    ... Do you mean that I change select query to union query and at the end to ... MS Access lets me create the report using the query. ... Does MS Access report support subquery? ...
    (microsoft.public.access.reports)
  • UNION in Subquery
    ... cq.qids from the other query in the union. ... This query should fix the above problem but it doesn't. ... I could be wrong but by using simple queries like this one, ... I can use COUNT and a subquery, ...
    (microsoft.public.access.queries)
  • Re: UNION in Subquery
    ... Both sides of the Union is exactly the same and since you used UNION and not ... > cq.qids from the other query in the union. ... > come to the conclusion that UNION cannot be used in a subquery when COUNT ... > combine the two queries into one. ...
    (microsoft.public.access.queries)
  • Unammed view
    ... I would like to report on sales from different tables using an unamed view ... query, which is like a subquery that has 3 UNION selects. ...
    (microsoft.public.vb.crystal)