Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Posts
    43

    Unanswered: Help with query - selecting min value

    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.

  2. #2
    Join Date
    Apr 2004
    Posts
    43
    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)

  3. #3
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    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

  4. #4
    Join Date
    Apr 2004
    Posts
    43
    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?

  5. #5
    Join Date
    Aug 2004
    Posts
    330
    Try removing wfm_astat.abt from the group by clause.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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 )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •