Getting max. screen resolution with Group By

Question

Question: I have a website where I gather browser statistics.

Thus, I have an SQL table (T_Visits), with the following columns:
uniqueidentifier Visit_UID,
uniqueidentifier User_UID,
datetime Visit_DateTime,
float Screen_w,
float Screen_h,
float Resolution = Screen_w * Screen_h
varchar resolutionstring = screen_w + ' x ' + screen_h


Since a user can visit the site from several computers, there can be different entries in screensize for each visit for the same user.
Now I want to get the maximum/minimum resolution each user had:

Select User_UID, max(resolution) from T_Visits GROUP BY User_UID

How can I get the corresponding resolution string ?
I mean I can get the max(screen_w) and max(screen_h), but there's no guarantee that the corresponding resolutionstring would be max(screen_w) +' x '+ max(screen_h)

Answer 1

SELECT DISTINCT T_Visits.User_UID, T_Visits.Resolution, T_Visits.resolutionstring
FROM T_Visits 
     INNER JOIN (SELECT User_UID, max(resolution) AS max 
                 FROM T_Visits 
                 GROUP BY User_UID) temp
          ON T_Visits.User_UID = temp.User_UID
             AND T_Visits.Resolution = temp.max

This query first creates a temp table of each users id and max resolution, then inner joins that with the T_Visits table matching the user id and resolution fields, which should give you the corresponding resolutionstring(s).

There are some problems though with this kind of query however. First off, while the DISTINCT takes care of multiple rows being returned for the same resolutionstring, it should still return multiple rows per user if they have multiple monitors with the same resolution. For example, what if someone visits your site with an iPhone, and you record a hit with 320x480, but then they turn their phone sideways and hit your site again, which should now register 480x320 because their X and Y values are now swapped due to orientation. This would produce multiple max resolution hits with different resolutionstrings.

The same thing can happen for monitors. It is not uncommon for document editors to rotate their monitors for a more "legal paper" style view. However, when they visit your site from their homes, they might not have the same setup, but do have the same resolution.

What exactly would you want your query to return if that is the case?

Answer 2

Try something like:

;WITH resCTE
AS
(
        SELECT User_UID
               ,resolutionstring
               ,ROW_NUMBER() OVER (PARTITION BY User_UID
                                   ORDER BY Resolution desc
                                            ,Screen_w desc
                                  ) AS rnMax
               ,ROW_NUMBER() OVER (PARTITION BY User_UID
                                   ORDER BY Resolution
                                            ,Screen_w
                                  ) AS rnMin                        
)
SELECT maxr.User_UID
       ,maxr.resolutionstring AS maxRes
       ,minr.resolutionstring AS minRes
FROM resCTE AS maxr
JOIN resCTE AS minr
ON   minr.User_UID = maxr.User_UID
AND  minr.rnMin    = 1
WHERE maxr.rnMax = 1

(untested)

Note that this assumes you only want to see 1 row per user id, regardless of whether more than one HxW gives the same resolution. It would be possible to modify the query to use RANK() rather than ROWNUMBER() if this isn't the behaviour you want.

EDIT

Amended to show the max/min resolution sub-sorted by screen width

Answer 3

This should work to get a list of User_UID with the string of the maximum resolution. Adapt it to get all the minimum resolutions. Maybe its not the most efficient way...

Select User_UID, resolutionstring from T_Visits as p
WHERE resolution = (Select max(resolution) from T_Visits where User_UID =p.User_UID)