title: Get records with highest/smallest <whatever> per group
body: <p>How to do that?</p>
  
  <p>Former title of this question was "<em>using rank (@Rank := @Rank + 1) in complex query with subqueries - will it work?</em>" because I was looking for solution using ranks, but now I see that the solution posted by Bill is much much better.</p>
  
  <p>Original question:</p>
  
  <p>I'm trying to compose a query that would take last record from each group given some defined order:</p>
  
  <pre><code>SET @Rank=0;
  
  select s.*
  from (select GroupId, max(Rank) AS MaxRank
        from (select GroupId, @Rank := @Rank + 1 AS Rank 
              from Table
              order by OrderField
              ) as t
        group by GroupId) as t 
    join (
        select *, @Rank := @Rank + 1 AS Rank
        from Table
        order by OrderField
        ) as s 
    on t.GroupId = s.GroupId and t.MaxRank = s.Rank
  order by OrderField
  </code></pre>
  
  <p>Expression <code>@Rank := @Rank + 1</code> is normally used for rank, but for me it looks suspicious when used in 2 subqueries, but initialized only once. Will it work this way?</p>
  
  <p>And second, will it work with one subquery that is evaluated multiple times? Like subquery in where (or having) clause (another way how to write the above):</p>
  
  <pre><code>SET @Rank=0;
  
  select Table.*, @Rank := @Rank + 1 AS Rank
  from Table
  having Rank = (select max(Rank) AS MaxRank
                from (select GroupId, @Rank := @Rank + 1 AS Rank 
                      from Table as t0
                      order by OrderField
                      ) as t
                where t.GroupId = table.GroupId
               )
  order by OrderField
  </code></pre>
  
  <p>Thanks in advance!</p>