MySQL Select rows on first occurrence of each unique value

MySQL Select rows on first occurrence of each unique value

If you want to select only the first occurrence of each unique value in MySQL, you can use the ROW_NUMBER() window function along with the PARTITION BY clause. Here's an example:

Assuming you have a table named your_table with a column named your_column:

SELECT * FROM ( SELECT your_column, ROW_NUMBER() OVER (PARTITION BY your_column ORDER BY some_ordering_column) AS row_num FROM your_table ) AS ranked WHERE row_num = 1;

Replace your_table with the actual name of your table, your_column with the column containing the values you want to filter, and some_ordering_column with the column by which you want to define the order of the rows within each partition.

Explanation:

  • The ROW_NUMBER() function assigns a unique number to each row within a partition defined by your_column.
  • The PARTITION BY clause ensures that the numbering restarts for each unique value in your_column.
  • The ORDER BY clause within ROW_NUMBER() defines the order of rows within each partition.
  • The outer query selects only rows where the row_num is equal to 1, effectively selecting the first occurrence of each unique value.

Adjust the column and table names based on your actual schema.

Examples

  1. Using DISTINCT with GROUP BY:

    • SELECT DISTINCT Column1, Column2, ... FROM YourTable;
    • Description: Uses DISTINCT to select only the first occurrence of each unique combination of columns.

  2. Using GROUP BY with MIN/MAX Aggregate Functions:

    • SELECT Column1, MIN(Column2) AS MinColumn2, ... FROM YourTable GROUP BY Column1;
    • Description: Groups by the first column and uses MIN or MAX to select the first occurrence of each unique value in other columns.

  3. Using ROW_NUMBER() with Window Function:

    • SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY Column1 ORDER BY SomeColumn) AS RowNum FROM YourTable ) AS RankedTable WHERE RowNum = 1;
    • Description: Utilizes ROW_NUMBER() with PARTITION BY to assign row numbers and selects only rows with RowNum equal to 1 for each unique value in Column1.

  4. Using Subquery with JOIN:

    • SELECT T1.* FROM YourTable T1 JOIN ( SELECT Column1, MIN(Column2) AS MinColumn2, ... FROM YourTable GROUP BY Column1 ) AS T2 ON T1.Column1 = T2.Column1 AND T1.Column2 = T2.MinColumn2 AND ...;
    • Description: Uses a subquery with GROUP BY and JOIN to filter rows based on the first occurrence of each unique value.

  5. Using NOT EXISTS:

    • SELECT * FROM YourTable T1 WHERE NOT EXISTS ( SELECT 1 FROM YourTable T2 WHERE T2.Column1 = T1.Column1 AND T2.SomeColumn < T1.SomeColumn );
    • Description: Uses NOT EXISTS to select only rows where there is no earlier occurrence of the same value in Column1.

  6. Using JOIN with Subquery:

    • SELECT T1.* FROM YourTable T1 JOIN ( SELECT Column1, MIN(SomeColumn) AS MinSomeColumn FROM YourTable GROUP BY Column1 ) AS T2 ON T1.Column1 = T2.Column1 AND T1.SomeColumn = T2.MinSomeColumn;
    • Description: Uses JOIN with a subquery to filter rows based on the minimum value of SomeColumn for each unique value in Column1.

  7. Using GROUP_CONCAT() and SUBSTRING_INDEX:

    • SELECT SUBSTRING_INDEX(GROUP_CONCAT(Column2 ORDER BY SomeColumn), ',', 1) AS FirstColumn2, Column1, ... FROM YourTable GROUP BY Column1;
    • Description: Uses GROUP_CONCAT() to concatenate values in Column2, and SUBSTRING_INDEX to extract the first occurrence for each unique value in Column1.

  8. Using DISTINCT with ORDER BY:

    • SELECT DISTINCT Column1, Column2, ... FROM YourTable ORDER BY Column1, SomeColumn;
    • Description: Uses DISTINCT with ORDER BY to select the first occurrence of each unique combination of columns based on a specified order.

  9. Using LEFT JOIN with IS NULL:

    • SELECT T1.* FROM YourTable T1 LEFT JOIN YourTable T2 ON T1.Column1 = T2.Column1 AND T1.SomeColumn > T2.SomeColumn WHERE T2.Column1 IS NULL;
    • Description: Uses LEFT JOIN to match rows with earlier occurrences and filters to include only rows where there is no earlier occurrence.

  10. Using Subquery with NOT IN:

    • SELECT * FROM YourTable WHERE (Column1, SomeColumn) NOT IN ( SELECT Column1, MIN(SomeColumn) FROM YourTable GROUP BY Column1 );
    • Description: Uses a subquery with NOT IN to filter rows based on the first occurrence of each unique value in Column1 and SomeColumn.

More Tags

executequery jmx distributed-computing embedded-documents javascript-intellisense alphanumeric laravel-socialite maze probe gs-vlookup

More Programming Questions

More Retirement Calculators

More Dog Calculators

More Stoichiometry Calculators

More Statistics Calculators