Back to articles list May 21, 2014 - 2 minutes read How to Select the First Row in a Group? Agnieszka Kozubek-Krycuń Agnieszka is a Chief Content Officer at Vertabelo. Before coming to Vertabelo, she worked as a Java programmer. She has a PhD in mathematics and over 10 years of experience in teaching mathematics and computer science at the University of Warsaw. In her free time, she enjoys reading a good book, going mountain hiking and practicing yoga. Tags: PostgreSQL SQL Often you want to select a single row from each GROUP BY group. PostgreSQL has a statement especially for that: SELECT DISTINCT ON. Let's say I want to select one weather report for each location. location time report Ottawa 2014-05-15 8:00 sunny 2014-05-15 11:00 cloudy 2014-05-15 15:00 rainy Warsaw 2014-05-15 8:00 overcast 2014-05-15 11:00 sunny 2014-05-15 15:00 rainy SELECT DISTINCT ON (location) location, time, report FROM weather_reports; The query retrieves one weather report for each location. You can use several expressions in SELECT DISTINCT ON statement. SELECT DISTINCT ON (creation_date_week, resource_id) id, creation_date, date_trunc('week', creation_date) creation_date_week, resource_id FROM backup_data; For each resource the query retrieves a single backup data for each week. SELECT DISTINCT ON with ORDER BY The undecorated SELECT DISTINCT ON selects one row for each group but you don't know which of the rows will be selected. Each execution of the query can return different rows. Use the ORDER BY clause if you want to select a specific row. SELECT DISTINCT ON (location) location, time, report FROM weather_reports ORDER BY location, time DESC; The query retrieves the most recent weather report for each location. SELECT DISTINCT ON (creation_date_week, resource_id) id, creation_date, date_trunc('week', creation_date) creation_date_week, resource_id FROM backup_data ORDER BY creation_date_week, resource_id, creation_date DESC; For each resource the query selects the newest backup data in each week. The SELECT DISTINCT ON expressions have to repeat at the beginning of the ORDER BY clause. You can add additional expression after them. SELECT DISTINCT A related clause is the standard SELECT DISTINCT clause. The standard clause eliminates duplicate rows. SELECT DISTINCT ON eliminates rows duplicating the given expressions. Tags: PostgreSQL SQL You may also like DELETE RETURNING clause in PostgreSQL The standard DELETE statement in SQL returns the number of deleted rows. In PostgreSQL the DELETE RETURNING statement return can something else. Read more How To Learn The SELECT Statement in SQL Read our article to learn about the SELECT statement in SQL, one of the most important SQL instructions. Read more What Is a Nested Query in SQL? Have you ever faced a problem where it would be great to put a SELECT inside another SELECT statement? Learn how to use a nested query in SQL. Read more How Do You Write a SELECT Statement in SQL? Let’s start with the basics. Learn the syntax of a SELECT statement with simple examples, and start querying databases right away. Read more SQL Basics Cheat Sheet This 2-page SQL Basics Cheat Sheet will be a great value for beginners as well as for professionals. Download it in PDF or PNG format. Read more 5 Examples of GROUP BY Do you know how to effectively use the SQL GROUP BY clause? We provide five real-life GROUP BY examples seen in the business world. Read more Using GROUP BY in SQL Master the powerful SQL GROUP BY command. Learn to compute statistical summaries with aggregate functions to solve real-world Business Intelligence challenges. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.