Select first row in each GROUP BY group?

Use ARRAY_AGG function for PostgreSQL, U-SQL, IBM DB2, and Google BigQuery SQL:

SELECT customer, (ARRAY_AGG(id ORDER BY total DESC))[1], MAX(total)
FROM purchases
GROUP BY customer

In SQL Server you can do this:

SELECT *
FROM (
SELECT ROW_NUMBER()
OVER(PARTITION BY customer
ORDER BY total DESC) AS StRank, *
FROM Purchases) n
WHERE StRank = 1

Explaination:Here Group by is done on the basis of customer and then order it by total then each such group is given serial number as StRank and we are taking out first 1 customer whose StRank is 1

Snowflake/Teradata supports QUALIFY clause which works like HAVING for windowed functions:

SELECT id, customer, total
FROM PURCHASES
QUALIFY ROW_NUMBER() OVER(PARTITION BY p.customer ORDER BY p.total DESC) = 1

In PostgreSQL, another possibility is to use the first_value window function in combination with SELECT DISTINCT:

select distinct customer_id,
                first_value(row(id, total)) over(partition by customer_id order by total desc, id)
from            purchases;

I created a composite (id, total), so both values are returned by the same aggregate. You can of course always apply first_value() twice.

This way it work for me:

SELECT article, dealer, price
FROM   shop s1
WHERE  price=(SELECT MAX(s2.price)
              FROM shop s2
              WHERE s1.article = s2.article
              GROUP BY s2.article)
ORDER BY article;

Select highest price on each article

This is how we can achieve this by using windows function:

    create table purchases (id int4, customer varchar(10), total integer);
    insert into purchases values (1, 'Joe', 5);
    insert into purchases values (2, 'Sally', 3);
    insert into purchases values (3, 'Joe', 2);
    insert into purchases values (4, 'Sally', 1);
    
    select ID, CUSTOMER, TOTAL from (
    select ID, CUSTOMER, TOTAL,
    row_number () over (partition by CUSTOMER order by TOTAL desc) RN
    from purchases) A where RN = 1;

• If you want to select any (by your some specific condition) row from the set of aggregated rows.

• If you want to use another (sum/avg) aggregation function in addition to max/min. Thus you can not use clue with DISTINCT ON

You can use next subquery:

SELECT  
    (  
       SELECT **id** FROM t2   
       WHERE id = ANY ( ARRAY_AGG( tf.id ) ) AND amount = MAX( tf.amount )   
    ) id,  
    name,   
    MAX(amount) ma,  
    SUM( ratio )  
FROM t2  tf  
GROUP BY name

You can replace amount = MAX( tf.amount ) with any condition you want with one restriction: This subquery must not return more than one row

But if you wanna to do such things you probably looking for window functions

For SQl Server the most efficient way is:

with
ids as ( --condition for split table into groups
    select i from (values (9),(12),(17),(18),(19),(20),(22),(21),(23),(10)) as v(i) 
) 
,src as ( 
    select * from yourTable where  <condition> --use this as filter for other conditions
)
,joined as (
    select tops.* from ids 
    cross apply --it`s like for each rows
    (
        select top(1) * 
        from src
        where CommodityId = ids.i 
    ) as tops
)
select * from joined

and don't forget to create clustered index for used columns

This can be achieved easily by MAX FUNCTION on total and GROUP BY id and customer.

SELECT id, customer, MAX(total) FROM  purchases GROUP BY id, customer
ORDER BY total DESC;

My approach via window function dbfiddle:

1. Assign row_number at each group: row_number() over (partition by agreement_id, order_id ) as nrow
2. Take only first row at group: filter (where nrow = 1)

with intermediate as (select 
 *,
 row_number() over ( partition by agreement_id, order_id ) as nrow,
 (sum( suma ) over ( partition by agreement_id, order_id ))::numeric( 10, 2) as order_suma,
from <your table>)

select 
  *,
  sum( order_suma ) filter (where nrow = 1) over (partition by agreement_id)
from intermediate