Help with query - selecting min value

steve_o · #1 (**permalink**) 09-14-04, 03:45

I have a table (wfm_astat) that looks like this:

auftrag | af
--------+----
1311703 | 30
1311703 | 40
1400065 | 20
1400065 | 30
1400065 | 40

I have another table (wfm_auftrag) that looks like this:

auftrag |
--------+
1311703 |
1311704 |
1400065 |
1400066 |
1400067 |

I am trying to create a query that returns the record with the smallest 'af' number from the wfm_astat table, for all records that are also in the wfm_auftrag table. So my result would look something like this:

auftrag | af
--------+----
1311703 | 30
1400065 | 20

Can anybody give me some ideas on how to select the record with the smallest 'af' value??

Any help would be appreciated.
Thanks, stephen.

steve_o · #2 (**permalink**) 09-14-04, 04:45

In case anybody else is looking for an answer to this problem, here it is:

Code:

SELECT wfm_astat.auftrag, wfm_astat.af
FROM wfm_astat
WHERE wfm_astat.auftrag = wfm_auftrag.auftrag
AND wfm_astat.af = (
                    SELECT min(af)
                    FROM wfm_astat
                    WHERE wfm_astat.auftrag = wfm_auftrag.auftrag)

RBARAER · #3 (**permalink**) 09-14-04, 12:14

Self-answering : good job !

This would also work :

SELECT wfm_astat.auftrag, min(wfm_astat.af)
FROM wfm_astat, wfm_auftrag
WHERE wfm_astat.auftrag = wfm_auftrag.auftrag
GROUP BY wfm_astat.auftrag;

Regards,

RBARAER

steve_o · #4 (**permalink**) 10-20-04, 02:22

I have a table (wfm_astat) that looks like this:

auftrag | af | abt |
--------+----+-----+
1311703 | 30 | D00 |
1311703 | 40 | F00 |
1400065 | 20 | C12 |
1400065 | 30 | C23 |
1400065 | 40 | F00 |

I have another table (wfm_auftrag) that looks like this:

auftrag |
--------+
1311703 |
1311704 |
1400065 |
1400066 |
1400067 |

I am trying to create a query that returns the record with the smallest 'af' number from the wfm_astat table, for all records that are also in the wfm_auftrag table. So my result would look something like this:

auftrag | af | abt
--------+----+----
1311703 | 30 | D00
1400065 | 20 | C12

The query from RBARAER worked fine when I only had the two columns in the wfm_astat table, but now that there is the extra 'abt' field, I no longer get only the minimum 'af' value per 'auftrag'. Here is my query that returns the wrong results:

Code:

SELECT wfm_astat.auftrag, min(wfm_astat.af), wfm_astat.abt
FROM wfm_astat, wfm_auftrag
WHERE wfm_astat.auftrag = wfm_auftrag.auftrag
GROUP BY wfm_astat.auftrag, wfm_astat.abt;

Can anybody tell me what I am doing wrong here?

urquel · #5 (**permalink**) 10-20-04, 10:58

Try removing wfm_astat.abt from the group by clause.

r937 · #6 (**permalink**) 10-21-04, 21:21

urquel, that would require removing it from the SELECT list as well (so as not to cause a syntax error), but then the query would no longer return the right number of columns

steve, try this:

Code:

select wfm_astat.auftrag
     , wfm_astat.af
     , wfm_astat.abt
  from wfm_astat   as x
inner
  join wfm_auftrag
    on wfm_astat.auftrag
     = wfm_auftrag.auftrag
 where wfm_astat.af
     = ( select min(af)
           from wfm_astat
          where auftrag = x.auftrag )

Internet Services

Web Development

Digital Marketing

Consumer Tech