Finding values that matches max column in SQL server

Question

I have a table that has the max months column like the one below:

   Category  MaxMonths Title
    X         3         Beginner-1
    X         6         Intermediate-1
    X         12        Avance-1
    X         999       Master-1
    Y         3         Beginner-2
    Y         6         Intermediate-2
    Y         12        Avance-2
    Y         999       Master-2

I also have another table with a number of months column like the one below:

  User #months   Category
    A     1        X
    B     5        X
    C     6        y
    D     12       y
    E     15       X

How can I write a case statement that shows based on the #months in the second table, what the user's title is?

For example, it needs to show user A's title is Beginner-1. I am able to join the tables using the category column but have a hard time matching the #months with maxMonths

score 0 · Answer 1 · answered Nov 11 '16 at 23:44

This query finds the maxMonths applicable for each user/category:

select t2.[User], t2.category, min(t1.maxMonths) as maxMonths
from table1 t1 
    inner join table2 t2 on t1.category = t2.category
        and t1.maxMonths >= t2.months
group by t2.[User], t2.category

You can then easily get the title:

select r1.[User], r2.Title
from
(select t2.[User], t2.category, min(t1.maxMonths) as maxMonths
from table1 t1 
    inner join table2 t2 on t1.category = t2.category
        and t1.maxMonths >= t2.months
group by t2.[User], t2.category) r1
inner join table1 r2 on r2.category = r1.category and r2.maxMonths = r1.maxMonths;

score 0 · Answer 2 · edited Sep 06 '17 at 21:51

0

Use the following query

Select 
    T1.User, 
    T2.Title 
from table1 T1 
inner join table2 T2 on t1.category = t2.category
Where t2.MaxMonthes = (
    Select 
        min(MaxMonthes) 
    from table1 T3 
    Where T3.maxmonthes >= T2.[#months] 
    and T2.category = T3.category
)

edited Sep 06 '17 at 21:51

Eli

2,538
1
25
36

answered Nov 12 '16 at 00:43

Hadi

36,233
13
65
124

score 0 · Answer 3 · answered Sep 06 '17 at 20:36

Here's your sample data in temp tables:

CREATE TABLE #title(
    category CHAR(1)
    ,maxMonths INT
    ,title VARCHAR(25)
)
GO

CREATE TABLE #months(
    [user] CHAR(1)
    ,months INT
    ,category CHAR(1)
)
GO

INSERT INTO #title VALUES 
('X',3  ,'Beginner-1')
,('X',6  ,'Intermediate-1')
,('X',12 ,'Avance-1')
,('X',999,'Master-1')
,('Y',3  ,'Beginner-2')
,('Y',6  ,'Intermediate-2')
,('Y',12 ,'Avance-2')
,('Y',999,'Master-2')
GO

INSERT INTO #months VALUES 
('A',1 ,'X')
,('B',5 ,'X')
,('C',6 ,'y')
,('D',12,'y')
,('E',15,'X')  
GO

You can use the following query to get what you're looking for.

SELECT
    #months.[user]
    ,#months.months
    ,#months.category
    ,#title.title
FROM #title
INNER JOIN #months ON #title.category = #months.category
WHERE #title.maxmonths = (
    SELECT 
        MIN(maxmonths)
    FROM #title
    WHERE maxmonths >= #months.months
)

Explanation: The nested query will limit the results to just the title with the maxMonths just above the actual months

Notes: I wasn't sure if you wanted a match on the months = maxMonth to go up a level or stay down. I assumed the latter, though you can change that by changing the >= in the nested query to >

Also, you used USER as a column name. You should refrain from using that in production environments, as it is a reserved keyword.

Finding values that matches max column in SQL server

3 Answers3