Get the First Row per Group

To get the first row per group in SQL, you can use the ROW_NUMBER function combined with a PARTITION BY clause. Here’s the basic syntax:

SELECT *  
FROM ( 
 SELECT *, ROW_NUMBER() OVER (PARTITION BY column_to_group_by ORDER BY column_to_order_by) as row_num 
FROM table_name  
) as grouped_table  
WHERE row_num = 1;

Replace column_to_group_by with the column you want to group by, and column_to_order_by with the column you want to order the rows by within each group.

The ROW_NUMBER function assigns a sequential integer to each row within the partition based on the order you defined. By selecting only rows with a row_num value of 1, you select only the first row for each group.

For example, let’s say you have a large dataset of customer orders that includes customer ID, order ID, order date, order total, and so on. You want to find the first order for each customer, so you can target them with special offers or promotions.

To achieve this, you could use the ROW_NUMBER in combination with a PARTITION BY clause to group the data by customer ID and order it by date. Then, you select only the rows with a ROW_NUMBER of 1 to find the first order for each customer.

The query would look like this:

SELECT * 
FROM ( 
 SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) as row_num 
 FROM orders 
) as grouped_orders 
WHERE row_num = 1;