2

I'm having problems with this select statement:

SELECT
  field1,
  MIN(field2) AS MinOfField2,
  (SELECT id FROM table2 WHERE something = MinOfField2) AS table2_id
FROM table1
GROUP BY field1

When I try to execute this query, access pops up a dialog asking me to enter the parameter value of 'MinOfField2'.

First I tried to use the aggregate function directly in the subquery, but that doesn't seem to be allowed either.

enter image description here

The closest existing question I could find is this one: Access alias in subquery

Community
  • 1
  • 1
Reto Höhener
  • 5,419
  • 4
  • 39
  • 79

1 Answers1

2

I believe that this is the query you are looking for: 

SELECT 
    t1.field1, 
    t1.MinOfField2,
    table2.id AS table2_id
FROM
    (
        SELECT 
            field1, 
            MIN(field2) AS MinOfField2
        FROM table1 
        GROUP BY field1
    ) t1
    INNER JOIN
    table2
        ON t1.MinOfField2=table2.something
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • Ok I think I see what you're doing. So basically the answer is that it's not possible to reference an alias in the subquery? – Reto Höhener Jun 12 '13 at 09:28
  • 2
    @Zalumon Correct, a sub query cannot reference an alias created outside of itself, hence why Gord has used a derived table with joins. As a side note, using derived tables is almost always faster than sub selects : ) – Matt Donnan Jun 12 '13 at 10:05
  • @Matt thanks for the confirmation. I believe that derived tables are faster, but for my taste this statement is already overly complicated (meaning that everytime I look at it in the future it will take me at least 15 mins to understand what it is doing). Subselects seem much more readable to me. I almost always pick readability over performance. I ended up solving this by doing my task in 2 steps, avoiding this kind of SQL altogether. – Reto Höhener Jun 12 '13 at 15:22
  • @Zalumon I'm sorry but I really must advise you to brush up on your SQL in that case, this is a very simple example of using a derived query and shouldn't be too difficult to read back. I'm not sure if the development is for you alone or perhaps a customer, but performance should often come before readability, especially if the differences are significant, which with large datasets they will be : ) – Matt Donnan Jun 13 '13 at 08:57