如何按item_id出现频次排序数据集并返回所有行？

Got it, let's figure out how to sort your entire dataset by how often each item_id shows up, while keeping every single row in the results. I'll break down the solutions for common databases below:

1. MySQL/MariaDB #

You can use a subquery to first calculate the occurrence count for each item_id, then join that back to your original table, and sort by the count:

SELECT t.*
FROM your_table t
JOIN (
    SELECT item_id, COUNT(*) AS occurrence_count
    FROM your_table
    GROUP BY item_id
) cnt ON t.item_id = cnt.item_id
ORDER BY cnt.occurrence_count DESC, t.id ASC;

The ORDER BY cnt.occurrence_count DESC sorts rows by how frequent their item_id is (most common first). Adding t.id ASC ensures rows with the same item_id stay in their original table order—feel free to swap this with another column if you prefer a different secondary sort.

2. PostgreSQL & SQL Server #

These databases support window functions, which let you calculate the occurrence count directly in the ORDER BY clause without needing a separate subquery join:

SELECT *
FROM your_table
ORDER BY COUNT(*) OVER (PARTITION BY item_id) DESC, id ASC;

The COUNT() OVER (PARTITION BY item_id) window function computes the total number of times each item_id appears, and we use that value to sort the entire dataset.

Quick Notes #

• Replace your_table with your actual table name
• Swap DESC with ASC if you want to sort from least frequent to most
• The secondary sort (like id ASC) is optional but helps keep results consistent when multiple item_ids have the same frequency

For example, using your sample data, the sorted result would look like this:

内容的提问来源于stack exchange，提问作者Curtis