List:General Discussion« Previous MessageNext Message »
From:Dan Bolser Date:April 11 2005 10:11am
Subject:Re: Display 1st row of every group?
View as plain text  
I guess that entry is either wrong or misleading.

I can get what I want like this

    SELECT * 
      FROM tbl 
INNER JOIN (	  SELECT id, min(bleah) as bleah
		    FROM tbl
		GROUP BY id
		)
    USING (id,bleah);

Which will work so long as bleah has a unique minimum value per id group.

On Mon, 11 Apr 2005, Dan Bolser wrote:

>
>I read with great interest this 
>
>http://www.artfulsoftware.com/queries.php#4
>
>Display 1st row of every group
>
>SELECT id
>FROM tbl
>GROUP BY id
>HAVING count(*) = 1;
>
>I want to use this syntax with an 'order by' like this...
>
>  SELECT *
>    FROM tbl
>GROUP BY id
>  HAVING count(*) = 1
>ORDER BY bleah;
>
>Will this syntax return the row within the "id group" with the smallest
>value of the bleah column? (is it guaranteed to do so?)
>
>Cheers,
>Dan.
>
>
>

Thread
Display 1st row of every group?Dan Bolser11 Apr
  • Re: Display 1st row of every group?Dan Bolser11 Apr
    • Re: Display 1st row of every group?Peter Brawley11 Apr
      • Re: Display 1st row of every group?Dan Bolser11 Apr
  • Re: Display 1st row of every group?Rhino11 Apr
    • Re: Display 1st row of every group?Dan Bolser11 Apr
      • Re: Display 1st row of every group?Peter Brawley11 Apr
        • Re: Display 1st row of every group?Dan Bolser11 Apr
          • Re: Display 1st row of every group?Peter Brawley11 Apr
            • Analyze TableEmmett Bishop11 Apr