Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Top Account Per Month

Author  Topic 

damdeok
Starting Member

39 Posts
Posted - 2010-11-30 : 16:15:19
Hi. Anyone help me make a query to list only the highest Registered Penny in a month? Thanks.

LuckyCoinsRank Table:


Site:

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts
Posted - 2010-11-30 : 17:48:28
Rather unspectacular, but this would do it. I am sure someone with more experience using rank can provide way better.


Create table LuckyCoinRanks (
ID int identity(1,1),
Account varchar(8) not null,
LuckyCoins int not null,
aDate datetime not null)
go

Insert into LuckyCoinRanks (Account,LuckyCoins,aDate)
Select 'admin',256,'20101001' UNION
Select 'admin',276,'20101201' UNION
Select 'heroes',272,'20101201' UNION
Select 'admin2',260,'20101201' UNION
Select 'daimos',255,'20101201' UNION
Select 'admin',276,'20101201' UNION
Select 'heroes',272,'20101201' UNION
Select 'admin2',260,'20101201' UNION
Select 'daimos',255,'20101201' UNION
Select 'admin2',253,'20101001'


GO
;WITH cteRank AS (

SELECT aDate, RANK() OVER (Partition by aDate Order by LuckyCoins desc ) as [RANK] ,LuckyCoins
FROM LuckyCoinRanks)


Select top 2 lcr.*
FROM LuckyCoinRanks lcr inner join cteRank
 on lcr.aDate = cteRank.aDate
  and lcr.LuckyCoins = cteRank.LuckyCoins

Go
drop table LuckyCoinRanks

/* Returns
ID	Account	LuckyCoins	aDate
1	admin	256	2010-10-01 00:00:00.000
2	admin	276	2010-12-01 00:00:00.000
*/



Poor planning on your part does not constitute an emergency on my part.
Go to Top of Page

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts
Posted - 2010-11-30 : 19:42:00
here's two different methods you can try.


drop table LuckyCoinRanks
Create table LuckyCoinRanks (
ID int identity(1,1),
Account varchar(8) not null,
LuckyCoins int not null,
aDate datetime not null)
go

Insert into LuckyCoinRanks (Account,LuckyCoins,aDate)
Select 'admin',256,'20101001' UNION all
Select 'admin',276,'20101201' UNION  all
Select 'heroes',272,'20101201' UNION  all
Select 'admin2',260,'20101201' UNION  all
Select 'daimos',255,'20101201' UNION  all
Select 'admin',276,'20101201' UNION  all
Select 'heroes',272,'20101201' UNION  all
Select 'admin2',260,'20101201' UNION  all
Select 'daimos',255,'20101201' UNION  all
Select 'admin2',253,'20101001'




Select *
from
(
select ROW_NUMBER() over (partition by dateadd(day,day(adate) * -1 + 1,adate) order by Max(luckycoins) desc) as myRank,MAX(LuckyCoins) as luckycoins,Account,dateadd(day,day(adate) * -1 + 1,adate) as mymonth
from LuckyCoinRanks
group by adate,account
) a
where a.myRank = 1

select *
from
(select  dateadd(day,day(adate) * -1 + 1,adate) as mymonth 
from
LuckyCoinRanks aa
group by dateadd(day,day(adate) * -1 + 1,adate)
) a
cross apply
(select top 1 * from
LuckyCoinRanks aa
where dateadd(day,day(adate) * -1 + 1,adate) = a.mymonth order by LuckyCoins desc)
b


Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2010-12-01 : 02:26:15
http://beyondrelational.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

damdeok
Starting Member

39 Posts
Posted - 2010-12-02 : 06:47:42
Thanks to all your replies. It fits my need.

I chose Vinnie881's query:

select *
from
(select dateadd(day,day(adate) * -1 + 1,adate) as mymonth
from
LuckyCoinRanks aa
group by dateadd(day,day(adate) * -1 + 1,adate)
) a
cross apply
(select top 1 * from
LuckyCoinRanks aa
where dateadd(day,day(adate) * -1 + 1,adate) = a.mymonth order by LuckyCoins desc)
b

I would appreciate if you can add MyRank to this query.
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts
Posted - 2010-12-02 : 06:59:26
What's MyRank?
Isn't it 1 as these are the highest ranked?
Maybe you want to rank the results across months

select *, MyRank = RANK() over (order by LuckyCoins desc)
from
(select dateadd(day,day(adate) * -1 + 1,adate) as mymonth
from
LuckyCoinRanks aa
group by dateadd(day,day(adate) * -1 + 1,adate)
) a
cross apply
(select top 1 * from
LuckyCoinRanks aa
where dateadd(day,day(adate) * -1 + 1,adate) = a.mymonth order by LuckyCoins desc)
b


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

damdeok
Starting Member

39 Posts
Posted - 2010-12-02 : 07:17:19
Actually id 6-9 should not be there because i have it duplicated.

I'd appreciate if you can add MyRank per month like this:



I used here "select top 2..."
Go to Top of Page

dineshrajan_it
Posting Yak Master

217 Posts
Posted - 2010-12-02 : 08:30:42
declare @LuckyCoinRanks table(
ID int identity(1,1),
Account varchar(8) not null,
LuckyCoins int not null,
aDate datetime not null)


Insert into @LuckyCoinRanks (Account,LuckyCoins,aDate)
Select 'admin',256,'20101001' UNION
Select 'admin',276,'20101201' UNION
Select 'heroes',272,'20101201' UNION
Select 'admin2',260,'20101201' UNION
Select 'daimos',255,'20101201' UNION
Select 'admin',276,'20101201' UNION
Select 'heroes',272,'20101201' UNION
Select 'admin2',260,'20101201' UNION
Select 'daimos',255,'20101201' UNION
Select 'admin2',253,'20101001'



select L2.Account,L2.aDate,L2.LuckyCoins,L2.TopRecord
from
(
select *,[TopRecord]= ROW_NUMBER() over (partition by DATEADD(day,0,datediff(day,0,adate)) order by luckycoins desc)
from
@LuckyCoinRanks L1
)L2
where
L2.TopRecord = 1

Iam a slow walker but i never walk back
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts
Posted - 2010-12-02 : 08:35:59
Bit confused. You have the top ranked by month (i.e. one row per month) but this resultset has two rows for 20101201.
If you want this you can't use the query you have selected.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

dineshrajan_it
Posting Yak Master

217 Posts
Posted - 2010-12-02 : 08:36:03
quote:
Originally posted by Vinnie881

here's two different methods you can try.


drop table LuckyCoinRanks
Create table LuckyCoinRanks (
ID int identity(1,1),
Account varchar(8) not null,
LuckyCoins int not null,
aDate datetime not null)
go

Insert into LuckyCoinRanks (Account,LuckyCoins,aDate)
Select 'admin',256,'20101001' UNION all
Select 'admin',276,'20101201' UNION  all
Select 'heroes',272,'20101201' UNION  all
Select 'admin2',260,'20101201' UNION  all
Select 'daimos',255,'20101201' UNION  all
Select 'admin',276,'20101201' UNION  all
Select 'heroes',272,'20101201' UNION  all
Select 'admin2',260,'20101201' UNION  all
Select 'daimos',255,'20101201' UNION  all
Select 'admin2',253,'20101001'




Select *
from
(
select ROW_NUMBER() over (partition by dateadd(day,day(adate) * -1 + 1,adate) order by Max(luckycoins) desc) as myRank,MAX(LuckyCoins) as luckycoins,Account,dateadd(day,day(adate) * -1 + 1,adate) as mymonth
from LuckyCoinRanks
group by adate,account
) a
where a.myRank = 1

select *
from
(select  dateadd(day,day(adate) * -1 + 1,adate) as mymonth 
from
LuckyCoinRanks aa
group by dateadd(day,day(adate) * -1 + 1,adate)
) a
cross apply
(select top 1 * from
LuckyCoinRanks aa
where dateadd(day,day(adate) * -1 + 1,adate) = a.mymonth order by LuckyCoins desc)
b


Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881


Hi,

Based upon your query is it advisable to use group by for account.
and i feel second query is bit consuming.

Pls correct me if iam wrong.

Iam a slow walker but i never walk back
Go to Top of Page

damdeok
Starting Member

39 Posts
Posted - 2010-12-02 : 09:04:15
quote:
Originally posted by nigelrivett

Bit confused. You have the top ranked by month (i.e. one row per month) but this resultset has two rows for 20101201.
If you want this you can't use the query you have selected.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Thanks for the solution on Top 1. I only need an additional query to show the Top 2 accounts per month.

I just thought by using this query it may give the result I expected,

quote:
select *, MyRank = RANK() over (order by LuckyCoins desc)
from
(select dateadd(day,day(adate) * -1 + 1,adate) as mymonth
from
LuckyCoinRanks aa
group by dateadd(day,day(adate) * -1 + 1,adate)
) a
cross apply
(select top 2 * from
LuckyCoinRanks aa
where dateadd(day,day(adate) * -1 + 1,adate) = a.mymonth order by LuckyCoins desc)
b


to show the Top 2 per month. Example:
quote:
October
-Top 1
-Top 2
November
-Top 1
-Top 2
December
-Top 1
-Top 2
Go to Top of Page

dineshrajan_it
Posting Yak Master

217 Posts
Posted - 2010-12-02 : 09:10:08
I hope this will get the top 2 records of each month


declare @LuckyCoinRanks table(
ID int identity(1,1),
Account varchar(8) not null,
LuckyCoins int not null,
aDate datetime not null)


Insert into @LuckyCoinRanks (Account,LuckyCoins,aDate)
Select 'admin',256,'20101001' UNION
Select 'admin',276,'20101201' UNION
Select 'heroes',272,'20101201' UNION
Select 'admin2',260,'20101201' UNION
Select 'daimos',255,'20101201' UNION
Select 'admin',276,'20101201' UNION
Select 'heroes',272,'20101201' UNION
Select 'admin2',260,'20101201' UNION
Select 'daimos',255,'20101201' UNION
Select 'admin2',253,'20101001'




select L2.Account,L2.aDate,L2.LuckyCoins,L2.TopRecord
from
(
select *,[TopRecord]= ROW_NUMBER() over (partition by DATEADD(day,0,datediff(day,0,adate)) order by luckycoins desc)
from
@LuckyCoinRanks L1
)L2
where
L2.TopRecord <3

Iam a slow walker but i never walk back
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts
Posted - 2010-12-02 : 09:17:08
In that case
select *, MyRank = RANK() over (partition by mymonth order by LuckyCoins desc)
from
(select dateadd(day,day(adate) * -1 + 1,adate) as mymonth
from
LuckyCoinRanks aa
group by dateadd(day,day(adate) * -1 + 1,adate)
) a
cross apply
(select top 2 * from
LuckyCoinRanks aa
where dateadd(day,day(adate) * -1 + 1,adate) = a.mymonth order by LuckyCoins desc)
b

change rank() to row_number() if you want equal ranks to have different values.
Note if you have 3 with the same value in the same month this will only return two of them.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

damdeok
Starting Member

39 Posts
Posted - 2010-12-02 : 09:29:55
Thanks, dineshrajan_it and nigelrivett. Both your solution works.
Go to Top of Page

Sachin.Nand

2937 Posts
Posted - 2010-12-02 : 09:31:09
[code]

Create table LuckyCoinRanks (
ID int identity(1,1),
Account varchar(8) not null,
LuckyCoins int not null,
aDate datetime not null)
go

Insert into LuckyCoinRanks (Account,LuckyCoins,aDate)
Select 'admin',256,'20101001' UNION all
Select 'admin',276,'20101201' UNION all
Select 'heroes',272,'20101201' UNION all
Select 'admin2',260,'20101201' UNION all
Select 'daimos',255,'20101201' UNION all
Select 'admin',276,'20101201' UNION all
Select 'heroes',272,'20101201' UNION all
Select 'admin2',260,'20101201' UNION all
Select 'daimos',255,'20101201' UNION all
Select 'admin2',253,'20101001'UNION all
Select 'admin2',253,'20100909'

select * from LuckyCoinRanks


select datename(mm,aDate)Month,(L.LuckyCoins)LuckyCoins from LuckyCoinRanks L1
cross apply(select top 2(LuckyCoins)LuckyCoins from LuckyCoinRanks L2 where datepart(mm,L1.aDate)=DATEPART(mm,L2.aDate)
order by l2.aDate)L group by aDate,L.LuckyCoins

drop table LuckyCoinRanks


[/code]

PBUH
Go to Top of Page

damdeok
Starting Member

39 Posts
Posted - 2010-12-02 : 09:38:16
quote:
Originally posted by Sachin.Nand



Create table LuckyCoinRanks (
ID int identity(1,1),
Account varchar(8) not null,
LuckyCoins int not null,
aDate datetime not null)
go

Insert into LuckyCoinRanks (Account,LuckyCoins,aDate)
Select 'admin',256,'20101001' UNION all
Select 'admin',276,'20101201' UNION  all
Select 'heroes',272,'20101201' UNION  all
Select 'admin2',260,'20101201' UNION  all
Select 'daimos',255,'20101201' UNION  all
Select 'admin',276,'20101201' UNION  all
Select 'heroes',272,'20101201' UNION  all
Select 'admin2',260,'20101201' UNION  all
Select 'daimos',255,'20101201' UNION  all
Select 'admin2',253,'20101001'UNION  all
Select 'admin2',253,'20100909'

select * from LuckyCoinRanks


select datename(mm,aDate)Month,(L.LuckyCoins)LuckyCoins from LuckyCoinRanks L1
cross apply(select top 2(LuckyCoins)LuckyCoins from LuckyCoinRanks L2 where datepart(mm,L1.aDate)=DATEPART(mm,L2.aDate)
order by l2.aDate)L group by aDate,L.LuckyCoins

drop table LuckyCoinRanks




PBUH



Thanks Sachin.Nand. Is it possible to deduct 1 month from the date?

I usually announce the winner every 1st day of the month. So, the winner of September, I announce it on October 1 12AM. That is the date on the table. The winner of November, I announce it on Dec 1, 12AM.
Go to Top of Page

Sachin.Nand

2937 Posts
Posted - 2010-12-02 : 09:47:02
Sorry I did not understand what exactly do you want.Please post your expected output.

PBUH
Go to Top of Page

damdeok
Starting Member

39 Posts
Posted - 2010-12-02 : 10:16:15
Using your code above:


Expected output:

Rank Month Account LuckyCoins
1 November 2010 admin 276
2 November 2010 heroes 272
1 September 2010 admin 256
2 September 2010 admin2 253
1 August 2010 admin 253
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts
Posted - 2010-12-02 : 10:30:49
Suspect this unless you have gone away from your preferred choice of query to base the result on.

In that case
select *, MyRank = RANK() over (partition by mymonth order by LuckyCoins desc)
from
(select dateadd(day,day(adate) * -1 + 1,adate) as mymonth
from
LuckyCoinRanks aa
group by dateadd(day,day(adate) * -1 + 1,adate)
) a
cross apply
(select top 2 * from
LuckyCoinRanks aa
where dateadd(day,day(adate) * -1 + 1,adate) = a.mymonth order by LuckyCoins desc)
b



==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

damdeok
Starting Member

39 Posts
Posted - 2010-12-02 : 11:28:50
quote:
Originally posted by nigelrivett

Suspect this unless you have gone away from your preferred choice of query to base the result on.

In that case
select *, MyRank = RANK() over (partition by mymonth order by LuckyCoins desc)
from
(select dateadd(day,day(adate) * -1 + 1,adate) as mymonth
from
LuckyCoinRanks aa
group by dateadd(day,day(adate) * -1 + 1,adate)
) a
cross apply
(select top 2 * from
LuckyCoinRanks aa
where dateadd(day,day(adate) * -1 + 1,adate) = a.mymonth order by LuckyCoins desc)
b

Can you please make mymonth to month name and year? like October 2010.
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts
Posted - 2010-12-02 : 11:38:27
select mymonth = DATENAME(mm,mymonth) + ' ' + DATENAME(yy,mymonth),
ID, Account, LuckyCoins, aDate,
MyRank = RANK() over (partition by mymonth order by LuckyCoins desc)
from
(select dateadd(day,day(adate) * -1 + 1,adate) as mymonth
from
LuckyCoinRanks aa
group by dateadd(day,day(adate) * -1 + 1,adate)
) a
cross apply
(select top 2 * from
LuckyCoinRanks aa
where dateadd(day,day(adate) * -1 + 1,adate) = a.mymonth order by LuckyCoins desc)
b


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site