To get the first row of data for each group, the most common method is to use the ROW\_NUMBER() window function. 1. Number each group of data through ROW\_NUMBER() OVER (PARTITION BY group\_column ORDER BY order\_column); 2. Filter out records with rn = 1 to obtain the first data in each group; 3. If you need to keep the first one, you can use RANK() or DENSE\_RANK() instead; 4. Different databases support differently, such as MySQL 5.x does not support window functions, and variables can be simulated, while Oracle also supports KEEP (DENSE\_RANK FIRST) writing; 5. If you only care about the "first value" of a certain column, you can use the GROUP BY aggregate function instead; 6. It is recommended to add indexes on the sorted fields to improve performance for large tables; 7. When sorting multiple fields, you can specify multiple conditions after ORDER BY. The core is to understand the window function mechanism and choose the appropriate implementation method according to the database version.
In SQL queries, if you need to get the first row of data for each group, it is usually done to use a window function. This is more flexible than traditional GROUP BY , because GROUP BY aggregates data, while window functions can be sorted and filtered while preserving the original rows.
Use ROW_NUMBER() window function
This is the most common and recommended method. You can use ROW_NUMBER() to number the rows in each group and filter out the rows with the number 1.
SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY group_column ORDER BY order_column) AS rn
FROM your_table
) AS ranking
WHERE rn = 1;-
group_columnis the field you want to group (such as department ID) -
order_columnis the field you sort in each group (such as time or fraction) - This query returns the first-sorted record in each group
Note: If you want to keep the first-ranked situation (such as the sorting fields of the two records are the same), you can use
RANK()orDENSE_RANK()instead.
Applicable to different databases of writing
Although the above writing applies to most modern SQL databases (such as PostgreSQL, SQL Server, MySQL 8, Oracle), it may be a little different if you are using older versions or certain specific databases.
- MySQL 5.x : does not support window functions, can be simulated with variables, but the writing method is more complicated
- SQLite : It supports window functions since 3.25 and can be used normally
- Oracle : Supports window functions, and can also be written using
KEEP (DENSE_RANK FIRST)
Let's give an alternative to Oracle:
SELECT group_column,
MAX(some_column) KEEP (DENSE_RANK FIRST ORDER BY order_column) AS first_value
FROM your_table
GROUP BY group_column;This writing method is suitable for only wanting to take the "first value" of a certain column, not the entire row.
A few tips for practical use
- If you only care about a few columns rather than the entire row, you can consider replacing it with
GROUP BY 聚合函數(shù), such asMAX(CASE WHEN rn=1 THEN col END) - If the table is large, remember to add an index on
order_columnto improve sorting efficiency - When sorting multiple fields, multiple conditions can be written after
ORDER BY, such asORDER BY create_date DESC, score ASC
Basically that's it. The core is to understand how window functions work, and then choose the appropriate syntax based on the database version you use.
The above is the detailed content of How to get the top 1 row for each group in SQL?. For more information, please follow other related articles on the PHP Chinese website!
Hot AI Tools
Undress AI Tool
Undress images for free
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Defining Database Schemas with SQL CREATE TABLE Statements
Jul 05, 2025 am 01:55 AM
In database design, use the CREATETABLE statement to define table structures and constraints to ensure data integrity. 1. Each table needs to specify the field, data type and primary key, such as user_idINTPRIMARYKEY; 2. Add NOTNULL, UNIQUE, DEFAULT and other constraints to improve data consistency, such as emailVARCHAR(255)NOTNULLUNIQUE; 3. Use FOREIGNKEY to establish the relationship between tables, such as orders table references the primary key of the users table through user_id.
Key Differences Between SQL Functions and Stored Procedures.
Jul 05, 2025 am 01:38 AM
SQLfunctionsandstoredproceduresdifferinpurpose,returnbehavior,callingcontext,andsecurity.1.Functionsreturnasinglevalueortableandareusedforcomputationswithinqueries,whileproceduresperformcomplexoperationsanddatamodifications.2.Functionsmustreturnavalu
Using SQL LAG and LEAD functions for time-series analysis.
Jul 05, 2025 am 01:34 AM
LAG and LEAD in SQL are window functions used to compare the current row with the previous row data. 1. LAG (column, offset, default) is used to obtain the data of the offset line before the current line. The default value is 1. If there is no previous line, the default is returned; 2. LEAD (column, offset, default) is used to obtain the subsequent line. They are often used in time series analysis, such as calculating sales changes, user behavior intervals, etc. For example, obtain the sales of the previous day through LAG (sales, 1, 0) and calculate the difference and growth rate; obtain the next visit time through LEAD (visit_date) and calculate the number of days between them in combination with DATEDIFF;
How to find columns with a specific name in a SQL database?
Jul 07, 2025 am 02:08 AM
To find columns with specific names in SQL databases, it can be achieved through system information schema or the database comes with its own metadata table. 1. Use INFORMATION_SCHEMA.COLUMNS query is suitable for most SQL databases, such as MySQL, PostgreSQL and SQLServer, and matches through SELECTTABLE_NAME, COLUMN_NAME and combined with WHERECOLUMN_NAMELIKE or =; 2. Specific databases can query system tables or views, such as SQLServer uses sys.columns to combine sys.tables for JOIN query, PostgreSQL can be used through inf
How to create a user and grant permissions in SQL
Jul 05, 2025 am 01:51 AM
Create a user using the CREATEUSER command, for example, MySQL: CREATEUSER'new_user'@'host'IDENTIFIEDBY'password'; PostgreSQL: CREATEUSERnew_userWITHPASSWORD'password'; 2. Grant permission to use the GRANT command, such as GRANTSELECTONdatabase_name.TO'new_user'@'host'; 3. Revoke permission to use the REVOKE command, such as REVOKEDELETEONdatabase_name.FROM'new_user
What is the SQL LIKE Operator and How Do I Use It Effectively?
Jul 05, 2025 am 01:18 AM
TheSQLLIKEoperatorisusedforpatternmatchinginSQLqueries,allowingsearchesforspecifiedpatternsincolumns.Ituseswildcardslike'%'forzeroormorecharactersand'_'forasinglecharacter.Here'showtouseiteffectively:1)UseLIKEwithwildcardstofindpatterns,e.g.,'J%'forn
How to backup and restore a SQL database
Jul 06, 2025 am 01:04 AM
Backing up and restoring SQL databases is a key operation to prevent data loss and system failure. 1. Use SSMS to visually back up the database, select complete and differential backup types and set a secure path; 2. Use T-SQL commands to achieve flexible backups, supporting automation and remote execution; 3. Recovering the database can be completed through SSMS or RESTOREDATABASE commands, and use WITHREPLACE and SINGLE_USER modes if necessary; 4. Pay attention to permission configuration, path access, avoid overwriting the production environment and verifying backup integrity. Mastering these methods can effectively ensure data security and business continuity.
When to use SQL subqueries versus joins for data retrieval.
Jul 14, 2025 am 02:29 AM
Whether to use subqueries or connections depends on the specific scenario. 1. When it is necessary to filter data in advance, subqueries are more effective, such as finding today's order customers; 2. When merging large-scale data sets, the connection efficiency is higher, such as obtaining customers and their recent orders; 3. When writing highly readable logic, the subqueries structure is clearer, such as finding hot-selling products; 4. When performing updates or deleting operations that depend on related data, subqueries are the preferred solution, such as deleting users that have not been logged in for a long time.
