Skip to content
Fix Code Error

Select first row in each GROUP BY group?

March 13, 2021 by Code Error
Posted By: Anonymous

As the title suggests, I’d like to select the first row of each set of rows grouped with a GROUP BY.

Specifically, if I’ve got a purchases table that looks like this:

SELECT * FROM purchases;

My Output:

id | customer | total
---+----------+------
 1 | Joe      | 5
 2 | Sally    | 3
 3 | Joe      | 2
 4 | Sally    | 1

I’d like to query for the id of the largest purchase (total) made by each customer. Something like this:

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

Expected Output:

FIRST(id) | customer | FIRST(total)
----------+----------+-------------
        1 | Joe      | 5
        2 | Sally    | 3

Solution

On Oracle 9.2+ (not 8i+ as originally stated), SQL Server 2005+, PostgreSQL 8.4+, DB2, Firebird 3.0+, Teradata, Sybase, Vertica:

WITH summary AS (
    SELECT p.id, 
           p.customer, 
           p.total, 
           ROW_NUMBER() OVER(PARTITION BY p.customer 
                                 ORDER BY p.total DESC) AS rk
      FROM PURCHASES p)
SELECT s.*
  FROM summary s
 WHERE s.rk = 1

Supported by any database:

But you need to add logic to break ties:

  SELECT MIN(x.id),  -- change to MAX if you want the highest
         x.customer, 
         x.total
    FROM PURCHASES x
    JOIN (SELECT p.customer,
                 MAX(total) AS max_total
            FROM PURCHASES p
        GROUP BY p.customer) y ON y.customer = x.customer
                              AND y.max_total = x.total
GROUP BY x.customer, x.total
Answered By: Anonymous

Related Articles

  • CSS Float: Floating an image to the left of the text
  • How to create a column with a count of rows between…
  • multilevel employee manager relationship through…
  • SQL query return data from multiple tables
  • How to retain value and compute based on group of…
  • How do I push items into an array in the data object…
  • Explanation on Integer.MAX_VALUE and…
  • Google in-app billing, a toast breaks everything
  • Reference - What does this regex mean?
  • onBillingSetupFinished never called
  • Sort table rows In Bootstrap
  • data.table vs dplyr: can one do something well the…
  • PostgreSQL DISTINCT ON with different ORDER BY
  • How to perform a conditional groupby calculation in…
  • SQL join: selecting the last records in a…
  • Android in-app-purchase valid order but not exist in…
  • python 3.2 UnicodeEncodeError: 'charmap' codec can't…
  • Iterating Through a Dictionary in Swift
  • How to reset a vue-infinite-loading element?
  • Counting records under multiple criteria in DAX
  • Discord.js .methods.purchase is not a function
  • Aggregate values in group by
  • Sorting A Table With Tabs & Javascript
  • Reloading an Element After Data Is Loaded in Polymer
  • Compare Two Json object's value, Find the Difference…
  • How do I pass data in $router.push in Vue.js?
  • How to make this polymer app responsive?
  • The result is not as intended, the output is a bound…
  • How to define partitioning of DataFrame?
  • how to make column bool data to be true with the…
  • Sending data in Laravel using Axios & Vue
  • SQL find sum of entries by date including previous date
  • How to add a regular list view in the extended part…
  • Select rows with unique values for one specific…
  • Combining items using XSLT Transform
  • CASE WHEN statement for ORDER BY clause
  • What can we use in Select Query instead of cursor
  • Get the second largest number in a list in linear time
  • Npm run serve Error
  • C# Nested Linq Group By
  • How to pass 2D array (matrix) in a function in C?
  • Ember data parent / child models and json
  • Vuejs 2 with Laravel 5.4 backend, post (Unauthorized) error
  • vuetify data table pagination custom function
  • Filter group-by based on records per group
  • How do I limit the number of rows returned by an…
  • Vuejs - cannot ready property of undefined - (but it…
  • sql query to find priority jobs
  • Calculate a Running Total in SQL Server
  • What is “the inverse side of the association” in a…
  • Using Auto Layout in UITableView for dynamic cell…
  • Filter child-records (hasMany association) with Ember.js
  • trying to create a dynamic swiper
  • Is there a way to join two queries in SQL each with…
  • Smart way to truncate long strings
  • Ember data FixtureAdapter hasmany - Cannot call…
  • Reading Data From Database and storing in Array List object
  • Individual click handlers in v-for loop
  • Can't find error with MS Access SQL FROM Clause Syntax
  • Convert array to nested JSON object - Angular Material tree
  • SELECT COUNT in LINQ to SQL C#
  • TableView 'click' listener being ignored
  • What is a NullReferenceException, and how do I fix it?
  • org.springframework.beans.factory.NoSuchBeanDefiniti…
  • Best way to select random rows PostgreSQL
  • Oracle 'Partition By' and 'Row_Number' keyword
  • How can building a heap be O(n) time complexity?
  • I'm working on pagination for my project. How do I…
  • typescript - cloning object
  • How to get WooCommerce order details
  • Vue Material - Styling the table buttons?
  • Vue - How do you render sub-rows in a table when you…
  • Heroku "Missing required flag -a --app" error after…
  • vueJS hide element in dynamic table row
  • POI Word Unable to merge newly created cell vertically
  • Generated row of table event listener not working instantly
  • When should I use a table variable vs temporary…
  • Print string to text file
  • C++ Need help sorting a 2D string array
  • Styling Polymer Element Dynamically
  • Compiler error: "class, interface, or enum expected"
  • React - componentWillReceiveProps alternative
  • How to have multiple functions in onkeyup?
  • Do duplicate values in the JOIN key affect the…
  • Best way to extract messy HTML tables using BeautifulSoup
  • Query Editor - Perform a step based on a Parameter Value
  • How to create a temporary table in SSIS control flow…
  • Web components and JS DOM Elements with a placeholder
  • Get top n records for each group of grouped results
  • Throw HttpResponseException or return…
  • org.hibernate.hql.internal.ast.QuerySyntaxException:…
  • How to solve Interpolation inside attributes has…
  • Simple Random Samples from a Sql database
  • So can't use weasyprint what is the best thing to do?
  • Iron-data-table fails to populate data from databinding
  • Ember get not getting certain attribute
  • Power Query: Calculate date/time instances within…
  • Iterate and sum values based on a condition in pandas
  • How to programmatically inject content in…
  • Polymer 1.0 'array-style' path accessors,…

Disclaimer: This content is shared under creative common license cc-by-sa 3.0. It is generated from StackExchange Website Network.

Post navigation

Previous Post:

Auto increment primary key in SQL Server Management Studio 2012

Next Post:

Making a div vertically scrollable using CSS

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

.net ajax android angular arrays aurelia backbone.js bash c++ css dataframe ember-data ember.js excel git html ios java javascript jquery json laravel linux list mysql next.js node.js pandas php polymer polymer-1.0 python python-3.x r reactjs regex sql sql-server string svelte typescript vue-component vue.js vuejs2 vuetify.js

  • you shouldn’t need to use z-index
  • No column in target database, but getting “The schema update is terminating because data loss might occur”
  • Angular – expected call-signature: ‘changePassword’ to have a typedeftslint(typedef)
  • trying to implement NativeAdFactory imports deprecated method by default in flutter java project
  • What should I use to get an attribute out of my foreign table in Laravel?
© 2022 Fix Code Error