group by nvarchar

Question

First of all, sorry for my English I will try to explain in the best way.

Is there a way to group by a column of type nvarchar in SQL Server 2008?

I am going to explain it better: (at least I try)

If I have a table like this:

ID  Quantity   City   Code   WebSiteLink
__  ________   ____   ____   __________
1      10      Milan   R2    home/images/1.jpg
2      20      Milan   R2    home/images/2.jpg
3      30      Rome    R2    home/images/3.jpg
4      55      Rome    R2    home/images/4.jpg
5      4       Naple   R2    home/images/5.jpg
6      1       London  R2    home/images/6.jpg
7      30      London  R2    home/images/7.jpg
8      40      London  R2    home/images/8.jpg

I am able to group all columns except WebSitelink because it is an nvarchar type.

My need is to get a random img link, is that possible to do it with a query?

I don't want to use a stored procedure.

Answer 1

My need is to get a random img link:

For example, if you need a random image for each code, e.g. GROUP BY code, you can use this query:

  SELECT Code, (SELECT TOP(1) A.WebSiteLink
                  FROM tbl A
                 WHERE A.Code = B.Code
              ORDER BY NEWID()) RandomWebSiteLink
    FROM tbl B
GROUP BY Code

Or the windowing function. Here is "groups by" City, Code and shows one random WebSiteLink

  SELECT City, Code, WebSiteLink
    FROM
       (
  SELECT City, Code, WebSiteLink,
         Row_Number() OVER (Partition by City, Code
                            Order by NewID()) RowNum
    FROM tbl
       ) X
   WHERE RowNum = 1;