SQL query to get most prevalent value in a column
I have two tables as below -
Sale Record:
Date | Customer | ItemSold
-----------------------------------------
11/01/2013 | Alex | Pen
12/01/2013 | Rony | Paper
13/01/2013 | Alex | Eraser
14/01/2013 | Marty | Eraser
15/01/2013 | Alex | Pen
16/01/2013 | Rob | Paper
17/01/2013 | Alex | Pencil
18/01/2013 | Alex | Pen
19/01/2013 | Ned | Pen
20/01/2013 | Alex | Paper
21/01/2013 | Alex | Pencil
22/01/2013 | Ned | Pen
23/01/2013 | Alex | Eraser
24/01/2013 | Alex | Pen
25/01/2013 | Alex | Pen
26/01/2013 | Alex | Paper
27/01/2013 | Ned | Paper
28/01/2013 | Alex | Pen
29/01/2013 | Alex | Eraser
30/01/2013 | Alex | Pen
31/01/2013 | Rony | Pencil
01/02/2013 | Alex | Eraser
02/02/2013 | Ned | Paper
03/02/2013 | Alex | Pen
Priority:
ItemName | Priority
--------------------------
Pen | 1
Paper | 2
Pencil | 3
Eraser | 4
I want to get a list to know which customer is likely to buy what as below -
Name | Item
----------------
Alex | Pen
Rob | Paper
Ned | Pen
Marty | Eraser
Rony | Paper
If there is a tie with items, the item with highest priority should be
selected. Ned bought Pen and Paper each twice but Pen should be selected
as it has more priority than paper.
What will be the sql query for this ?
No comments:
Post a Comment