Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Select DISTINCT but return all columns with MAX(field)?

I am trying to create a SELECT statement that selects all (*) the fields from a table that meet the following:

1 row for each DISTINCT( client_id, fileno, ppyear) combination, but only including the MAX(ppyear)

Based on what I have read, I believe I need to do a SELECT with SUBGROUP like

SELECT * FROM ppropdb
where concat(ppropdb.ppyear,ppropdb.fileno) 
IN (SUBGROUPQUERY)

I have tried to build a subquery to select them:

SELECT ppropdb.ppyear, ppropdb.client_id, ppropdb.fileno
FROM ppropdb
GROUP BY concat(ppropdb.ppyear,ppropdb.fileno)  
ORDER BY client_id,fileno,ppyear

but this does not return the MAX(ppyear) rows

Tags:

2

Answers


  1. 0 
    /*If you are trying to get files by client created last time, below code may help you*/

Drop table if exists #ppropdb

    Create table #ppropdb(client_id INT,ppyear INT,fileno INT)
    insert into #ppropdb values(1,2024,1),(1,2024,2),(1,2023,3),
    (1,2023,1),(1,2022,2),(2,2021,3),(2,2024,4)

    SELECT * FROM #ppropdb o
    where ppyear IN (SELECT MAX(ppyear) FROM #ppropdb i 
    Where i.client_id=o.client_id)

/*Using window function*/
    ;with CTE AS (
    SELECT *,ROW_NUMBER()OVER(Partition by client_id ORDER BY ppyear DESC,fileno DESC)latestFIleNo, 
    DENSE_RANK()OVER(Partition by client_id ORDER BY ppyear DESC)All_latest_per_year
    FROM #ppropdb o
    )SELECT * FROM CTE
    WHERE latestFIleNo=1  --If you want to get only single latest file then use this filter
    --All_latest_per_year=1 --If you want to get all file for max year then use this filter
    Login or Signup to reply.
  2. 0

    how about using a windowing function such as rownum select the max ppyear and group it by client_id, fileno. then select the columns where your rownum = 1 someting like below

    WITH RankedData AS (
    SELECT 
        client_id,
        fileno,
        ppyear,
        other_columns,
        ROW_NUMBER() OVER (PARTITION BY client_id, fileno ORDER BY ppyear DESC) AS rownum
    FROM 
        your_table
)
SELECT 
    client_id,
    fileno,
    ppyear,
    other_columns
FROM 
    RankedData
WHERE 
    rownum = 1;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search