Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top


I have a table of Users:

|Username|UserType|Points|
|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:

SELECT *
FROM Users U
INNER JOIN (
    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?

share|improve this question
up vote 10 down vote accepted

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:

SELECT 
  u.Username, 
  u.UserType,
  u.Points,
  lv.Level
FROM Users u
CROSS APPLY
(
  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 |
share|improve this answer

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]
FROM Users AS U
INNER JOIN lvls AS L ON
    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]);
share|improve this answer
    
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 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 at 7:14
    
And how many users (rows in table Users) are there and how wide is that table? – ypercubeᵀᴹ Feb 16 at 7:17

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:

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

[SQL Fiddle Demo]

share|improve this answer

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

SELECT   U1.*,
         L1.Level

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
;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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