I have a table of Users:

|John    |A       |250   |
|Mary    |A       |150   |
|Anna    |B       |600   |

and Levels

|UserType|MinPoints|Level  |
|A       |100      |Bronze |
|A       |200      |Silver |
|A       |300      |Gold   |
|B       |500      |Bronze |

And I'm looking for a query to get the level for each user. Something along the lines of:

FROM Users U
    SELECT TOP 1 Level, U.UserName
    FROM Levels L
    WHERE L.MinPoints < U.Points
    ORDER BY MinPoints DESC
    ) UL ON U.Username = UL.Username

Such that the results would be:

|Username|UserType|Points|Level  |
|John    |A       |250   |Silver |
|Mary    |A       |150   |Bronze |
|Anna    |B       |600   |Bronze |

Does anyone have any ideas or suggestions on how could I do this without resorting to cursors?


Your existing query is close to something that you could use but you can get the result easily by making a few changes. By altering your query to use the APPLY operator and implementing CROSS APPLY. This will return the row that meets your requirements. Here's a version that you could use:

FROM Users u
  SELECT TOP 1 Level
  FROM Levels l
  WHERE u.UserType = l.UserType
     and l.MinPoints < u.Points
  ORDER BY l.MinPoints desc
) lv;

Here's a SQL Fiddle with a demo. This produces a result:

| Username | UserType | Points |  Level |
|     John |        A |    250 | Silver |
|     Mary |        A |    150 | Bronze |
|     Anna |        B |    600 | Bronze |

The following solution uses a common table expression that scans the Levels table once. In this scan, the "next" points level is found using the LEAD() window function, so you have MinPoints (from the row) and MaxPoints (the next MinPoints for the current UserType).

After that, you can simply join the common table expression, lvls, on UserType and the MinPoints/MaxPoints range, like so:

WITH lvls AS (
    SELECT UserType, MinPoints, [Level],
           LEAD(MinPoints, 1, 99999) OVER (
               PARTITION BY UserType
               ORDER BY MinPoints) AS MaxPoints
    FROM Levels)

SELECT U.*, L.[Level]
    U.UserType=L.UserType AND
    L.MinPoints<=U.Points AND
    L.MaxPoints> U.Points;

The advantage of using the window function is that you eliminate all sorts of recursive solutions and improve performance dramatically. For best performance, you would use the following index on the Levels table:

CREATE UNIQUE INDEX ... ON Levels (UserType, MinPoints) INCLUDE ([Level]);
  • Thanks for the quick response. Your query does give me the exact result I need, but it seems to be a bit slower than bluefeet's answer above using "CROSS APPLY". For my specific dataset, using your CTE takes about 10 seconds without an index, and 7 seconds with the index you suggested on Levels, whereas the above Cross Apply query takes just under 3 seconds (even without an index) – Lambo Jayapalan Feb 15 '16 at 22:35
  • @LamboJayapalan This query looks like it should at least as efficient as bluefeet's. Did you add this exact index (with the INCLUDE)? Also, do you have an index on Users (UserType, Points)? (it might help) – ypercubeᵀᴹ Feb 16 '16 at 7:14
  • And how many users (rows in table Users) are there and how wide is that table? – ypercubeᵀᴹ Feb 16 '16 at 7:17

Why not do it using only the rudimentary operations, INNER JOIN, GROUP BY, and MAX:

SELECT   U1.*,

FROM     Users AS U1

         INNER JOIN
          SELECT   U2.Username,
                   MAX(L2.MinPoints) AS QualifyingMinPoints
          FROM     Users AS U2
                   INNER JOIN
                   Levels AS L2
                   ON U2.UserType = L2.UserType
          WHERE    L2.MinPoints <= U2.Points
          GROUP BY U2.Username
         ) AS Q
         ON U1.Username = Q.Username

         INNER JOIN
         Levels AS L1
         ON Q.QualifyingMinPoints = L1.MinPoints
            AND U1.UserType = L1.UserType

I think you can use an INNER JOIN -as a performance issue you can also use LEFT JOIN instead- with ROW_NUMBER() function like this:

    Username, UserType, Points, Level
    SELECT u.*, l.Level,
      ROW_NUMBER() OVER (PARTITION BY u.Username ORDER BY l.MinPoints DESC) seq
        Users u INNER JOIN
        Levels l ON u.UserType = l.UserType AND u.Points >= l.MinPoints
    ) dt
    seq = 1;

SQL Fiddle Demo

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.