Prev: UPDATE using SUM
Next: Question on anatomy of a query
From: aceavl via SQLMonster.com on 10 Aug 2010 13:29
Hi!

i have a table "tickets" and i need the top 2 most visited stores for every
customer.


create table #Tickets(ID int primary key, Store int, Customer int)
insert #Tickets select 1,1,1
union all select 2,1,1
union all select 3,2,1
union all select 4,1,1
union all select 5,2,1
union all select 6,3,2
union all select 7,3,2
union all select 8,3,2
union all select 9,2,2
union all select 10,2,2
union all select 11,2,2
union all select 12,1,2
union all select 13,1,2
union all select 14,1,3
union all select 15,1,3
union all select 16,1,3
union all select 17,3,3
union all select 18,3,3
union all select 19,2,3
union all select 20,2,2
go
SELECT Customer, Store, COUNT(Store) AS StoreCount
FROM #Tickets
GROUP BY Customer, Store
ORDER BY Customer, StoreCount DESC

--the result is this
Cust Store Count
1 1 3
1 2 2
2 2 4
2 3 3
2 1 2
3 1 3
3 3 2
3 2 1

i need it to be the top 2 like this:
Cust Store Count
1 1 3
1 2 2
2 2 4
2 3 3
3 1 3
3 3 2

hope someone can help me :)

thanks!

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/201008/1

From: John Bell on 10 Aug 2010 15:27
On Tue, 10 Aug 2010 17:29:03 GMT, "aceavl via SQLMonster.com"
<u31059(a)uwe> wrote:

>Hi!
>
>i have a table "tickets" and i need the top 2 most visited stores for every
>customer.
>
>
>create table #Tickets(ID int primary key, Store int, Customer int)
>insert #Tickets select 1,1,1
>union all select 2,1,1
>union all select 3,2,1
>union all select 4,1,1
>union all select 5,2,1
>union all select 6,3,2
>union all select 7,3,2
>union all select 8,3,2
>union all select 9,2,2
>union all select 10,2,2
>union all select 11,2,2
>union all select 12,1,2
>union all select 13,1,2
>union all select 14,1,3
>union all select 15,1,3
>union all select 16,1,3
>union all select 17,3,3
>union all select 18,3,3
>union all select 19,2,3
>union all select 20,2,2
>go
>SELECT Customer, Store, COUNT(Store) AS StoreCount
> FROM #Tickets
> GROUP BY Customer, Store
> ORDER BY Customer, StoreCount DESC
>
>--the result is this
>Cust Store Count
>1 1 3
>1 2 2
>2 2 4
>2 3 3
>2 1 2
>3 1 3
>3 3 2
>3 2 1
>
>i need it to be the top 2 like this:
>Cust Store Count
>1 1 3
>1 2 2
>2 2 4
>2 3 3
>3 1 3
>3 3 2
>
>hope someone can help me :)
>
>thanks!

With 2005 and later you have the ROW_COUNT function and you can do
this

SELECT Customer, Store, StoreCount
FROM (
SELECT Customer, Store, StoreCount,
ROW_NUMBER() OVER ( PARTITION BY Customer
ORDER BY Customer ASC, StoreCount DESC ) As RowCnt
FROM (
SELECT Customer, Store, COUNT(Store) AS StoreCount
FROM #Tickets
GROUP BY Customer, Store
) A
) B
WHERE RowCnt < 3
ORDER BY Customer ASC, StoreCount DESC

John
From: aceavl via SQLMonster.com on 10 Aug 2010 16:17
thanks john!
that was exactll what i was hoping for!

:)

--
Message posted via http://www.sqlmonster.com

 | 
Pages: 1
Prev: UPDATE using SUM
Next: Question on anatomy of a query