SQL exclude rows containing nearly duplicate values filtered by grouped max values

Question

I have a table like this...

ID	User_ID	Skill	Skill_Level	Skill_ID
1	1	Project Manager	3	1
2	4	Teacher	6	2
3	2	Teacher	5	2
4	3	Administrator	2	3
5	3	Project Manager	3	1
6	1	Project Manager	4	1
7	4	Deputy Head	5	4
8	2	Teacher	6	2

For regulatory reasons we cannot delete old (historic) rows. I need to run a query that just returns the set of "User_ID" + "Skill" and "Best Skill-Level" (i.e. max value per person). The successful query should look like this... So, some people can have more than one skill (User_ID 3 and 4 in this example) and some people become more skilled in their skill (User_ID 1 and 2).

ID	User_ID	Skill	Skill_Level	Skill_ID
2	4	Teacher	6	2
4	3	Administrator	2	3
5	3	Project Manager	3	1
6	1	Project Manager	4	1
7	4	Deputy Head	5	4
8	2	Teacher	6	2

Have you a suggestion about the most efficient and shortest query code that can achieve this? There is a table [dbo].[skills] with PK skill_ID_PK a table [dbo].[people] with PK user_ID_PK and a table [dbo].[skillHistory] with FK skill_ID_PK and FK user_ID_PK.

Any help most welcomed.

At the bottom of your question you say you have three tables, and those tables seems fairly sensible. But at the start of your question you begin with "I have a table like this...", and then list columns that look like they would come from a join of two of the tables you mentioned. I would think that the `skills` table just has `{skill_id, skill(_name)}`, and the people table probably has `{user_id, user_name}`, and the `skillHistory` table has `{user_id, skill_id, skill_level}` (and maybe a date). No? If you actually post your DDL we don't have to guess. — allmhuran, Jun 27 '22 at 19:23
Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Stu, Jun 27 '22 at 19:56

score 3 · Accepted Answer · answered Jun 27 '22 at 18:02

3

Using a SQL Generator for Snowflake I found that using QUALIFY was the recommendation, but I don't think that works in MSSQL.

To do this without QUALIFY, you might try something like this:

MSSQL

SELECT *
FROM (
    SELECT *,
    ROW_NUMBER() OVER (PARTITION BY User_ID, Skill ORDER BY Skill_Level DESC) AS RN
    FROM table
    ) AS T
WHERE T.RN = 1;

For RDBMS that support QUALIFY,

SELECT 
  * 
FROM 
  table QUALIFY ROW_NUMBER() OVER (
    PARTITION BY User_ID, Skill 
    ORDER BY Skill_Level DESC
  ) = 1

answered Jun 27 '22 at 18:02

Josh

1,493
1
13
24

You, my friend, are a Genius. MS SQL worked perfectly. Thank you so much. – DrThirsty Jun 27 '22 at 21:45
...Humm, but why doesn't this work in MS Access 2019 the same as it does in MS SQL 2019...? – DrThirsty Jun 27 '22 at 22:48

score 1 · Answer 2 · answered Jun 28 '22 at 03:26

1

SELECT TOP 1 WITH TIES *
FROM table
ORDER BY ROW_NUMBER() OVER(PARTITION BY User_ID, Skill ORDER BY Skill_Level DESC)

answered Jun 28 '22 at 03:26

Vitaliy Zlobin

111
3

1

Thanks, that's another brilliant query and works great in SQL. Trouble is, neither brilliant SQL solution so far works in the MS Access 2019 built in version of SQL. Does anyone have any idea why? – DrThirsty Jun 28 '22 at 12:31
1

Because MS Access uses a proprietary syntax called Jet SQL which is slightly different than SQL. In Access, you would have to build a Query that is GROUP BY user_id, skill, MAX(Skill_Level) and then join that query back to the table on the 3 columns. But then I guess ties might screw things up. Maybe check this out https://stackoverflow.com/questions/21917637/achieving-row-number-partition-by-in-ms-access – Josh Jun 28 '22 at 15:03
I have no experience with Jet SQL and have looked at the link you sent, copied it and can't get it to work. Have you any other good links to convert T-SQL into Jet SQL, as many of the searches I have conducted offer massively varying suggestions...? – DrThirsty Jun 29 '22 at 11:29
Sorry @DrThirsty, I'm just not aware of any SQL Generators that work for JetSQL. It is very niche and not very popular, unfortunately. I'd be happy to try and help you out but I don't have Microsoft on my machine so the only way would be to connect externally and do it live. – Josh Jun 29 '22 at 15:17
@DrThirsty here is my blind attempt at helping w/ MSACCESS - if it doesnt work, you should post this attempt in a new Question and tag it with Access, maybe some experts there can help: https://gist.github.com/itsamejoshab/3b6865fbd67a4dda65d9a8d5a3fd22fb – Josh Jun 29 '22 at 15:28
1

Hi Josh, thanks that 3-step process worked in T-SQL and in Jet SQL in MS Access. Your expertise and help has been really helpful. Thank you very much. In fact, thank all of you who offered ideas. They all worked in T-SQL on SQL Server 2019 but MS Access 2019 didn't like some of the formulations. Lesson learnt by me. – DrThirsty Jul 01 '22 at 13:37

SQL exclude rows containing nearly duplicate values filtered by grouped max values

2 Answers2

Linked