SQL Favorite Color Quiz

Assuming the following table:

USER  COLOR  ORDER
==================
JOE   RED    1
JOE   BLUE   2 
PHIL  BLUE   3

Joe first chose red, then chose blue as 开发者_运维问答his favorite color... How do you write a query that returns:

COLOR COUNT
RED   1
BLUE  1

Where only Joe's first vote is counted?

---

You would get the lowest ORDER value for each USER, and then join that against the table again to get the COLOR for those records, then group by the COLOR to count them:

select y.COLOR, count(*) as COUNT
from (select USER, min(ORDER) as ORDER from TheTable group by USER) x
inner join TheTable y on x.USER = y.USER and x.ORDER = y.ORDER
group by COLOR

---

This is fundamentally the same as my answer here, where I provide additional explanation as well.

SELECT color,count(*) AS count
FROM votes AS v
JOIN (
    SELECT user,MIN(order) AS order
    FROM votes
    GROUP BY user
) AS v2 ON (v.user=v2.user AND v.order=v2.order)
GROUP BY color;

---

The hardest part was that most of the columns in the table are reserved keywords...

CREATE TABLE #Votes (
    [USER] VARCHAR(50),
    COLOR VARCHAR(50),
    [ORDER] INT
)

INSERT INTO #Votes ([USER], COLOR, [ORDER]) VALUES ('JOE', 'RED', 1)
INSERT INTO #Votes ([USER], COLOR, [ORDER]) VALUES ('JOE', 'BLUE', 2)
INSERT INTO #Votes ([USER], COLOR, [ORDER]) VALUES ('PHIL', 'BLUE', 2)

;WITH NumberedVotes AS (
    SELECT
        [USER],
        COLOR,
        ROW_NUMBER() OVER (PARTITION BY [USER] ORDER BY [ORDER]) AS RowNumber
    FROM #Votes
)
SELECT
    COLOR,
    COUNT(*) AS [COUNT]
FROM NumberedVotes
WHERE RowNumber = 1
GROUP BY COLOR
ORDER BY [COUNT] DESC

---

In TSQL version 2005 and following:

SELECT color, COUNT(color) AS VOTES FROM
(SELECT *, ROW_NUMBER() OVER (PARTITION BY [user] ORDER BY [order]) AS Row FROM @votes) T
WHERE T.Row = 1 GROUP BY color

---

declare @tb table ( [user] varchar(10), color varchar(10), [order] int  identity (1,1))

insert @tb ([user],color) 
select 'JOE'  [user], 'RED' color 
union all
select 'JOE' [user], 'BLUE' color 
union all
select 'PHIL' [user], 'BLUE' color 


select color, count(user) from
(
select distinct p.color,t.[user] from @tb t 
        cross apply (select top 1 color from @tb e where e.[user] = t.[user] order by [order]) p
) u
group by color