Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2004
    Posts
    96

    Unanswered: Group Min Record

    Hello Guyz,

    A small problem here, I have the below table and I need to group and display the record that has the minimum value in the table (this table is derived from a query that permutates some records to give me this result).

    F1 F2 F3
    QQQ C 2
    QQQ B 1
    QQQ A 3

    expected result:
    QQQ B 1

    my result:
    when I group by F1, First(F2) and MIN(F3):
    QQQ C 1

    when I group by F1, MIN(F2) and MIN(F3):
    QQQ A 1

    when I group by F1, F2 and MIN(F3):
    QQQ C 2
    QQQ B 1
    QQQ A 3

    Any help would be very much appreciated..


    Cyherus
    Last edited by Cyherus; 09-16-04 at 01:04.

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    You don't GROUP BY the aggregate for one thing. What you need is something like this in this particular example:

    SELECT
    t1.F1,
    t1.F2,
    t1.F3
    FROM
    table t1
    INNER JOIN (
    SELECT MIN(F3) AS F3 FROM table) t2 ON t1.F3 = t2.F3

    This is assuming you're using SQL Server, which you probably aren't.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

Posting Permissions

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