Solved

Ranking by Group

Posted on 2011-03-18
10
273 Views
Last Modified: 2013-11-05
I have a simple table, ER, with the fields ID, Date and ExpRet.  I want to get ranks for every date based on ExpRet.  In other words, on 4/30/2011, I want to know the top ExpRet for that day and do that for every day.

Based on some internet research,  I thought I could do the following:

Rank: (SELECT ER.Date, (SELECT Count(*) FROM ER AS T WHERE T.Date = ER.Date AND T.ID <= ER.ID ORDER BY T.ID) AS ExpRet;)

However, I'm getting a syntax error and can't figure out what I'm doing wrong.

Thanks for any help!
0
Comment
Question by:kobys
  • 5
  • 2
  • 2
  • +1
10 Comments
 

Author Comment

by:kobys
Comment Utility
I should add that I'm putting this line in the Field: part of the query in design view.
0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
Comment Utility
Can you post some sample data with expectd result? anyways you can try this query.
select *,(select count(*) from ET as t2 where t2.Date = t1.Date and t2.ID <= t1.ID) as Rank
  from ER as t1

Open in new window

0
 

Author Comment

by:kobys
Comment Utility
Thanks Sharath.  Here's what some of the data looks like:

 
ID	Date	Ticker	ExpRet
1372	5/31/2007	CTL	-0.338981524414466
1629	6/30/2007	CTL	-0.336634294895352
317	1/31/2007	CTL	-0.336494063538555
197	12/31/2006	RHD	-0.335643798739565
1838	6/30/2007	XRX	-0.33358471788316
1578	5/31/2007	XRX	-0.332439739708935
1108	4/30/2007	CTL	-0.331870851906604
54	12/31/2006	CTL	-0.331486603335907
842	3/31/2007	CTL	-0.330538718263302
580	2/28/2007	CTL	-0.329058630981533
260	12/31/2006	XRX	-0.328599303125599
504	1/31/2007	USM	-0.32829976124678
471	1/31/2007	SEE	-0.326405483335948
241	12/31/2006	USM	-0.326352201512605

Open in new window


In your code above do you mean ER instead of ET?  also, are t1 and t2 also some kind of table variables?

I tried

Rank: (select *,(select count(*) from ER as t2 where t2.Date = t1.Date and t2.ID <= t1.ID) as Rank  from ER as t1;)

but still getting syntax error.

Thanks, Susan
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
please clarify what database you are using!

anyhow, please see this generic article that should solve your problem:
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/A_1555-Analytical-SQL-Where-do-you-rank.html

in case your date field would have time portion also, you need to sure you "group by" the date portion only, where this article might come into play:
http://www.experts-exchange.com/A_1499.html

hope this helps
0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
I don't see any syntactical error. How are you running the query?
SELECT *, 
       (SELECT COUNT(* ) 
          FROM ER AS t2 
         WHERE t2.DATE = t1.DATE 
               AND t2.ID <= t1.ID) AS Rank 
  FROM ER AS t1

Open in new window

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 3

Expert Comment

by:ssisworo
Comment Utility
try this :

SELECT Max(DATE) AS TopDATE, Max(ExpRet) AS TopExpRet
FROM ER
GROUP BY ER.DATE;
0
 
LVL 3

Expert Comment

by:ssisworo
Comment Utility
if you want get ID which has the top ExpRet of DATE, list of DATE, and the top ExpRet of DATE :

SELECT *  FROM ER
WHERE DATE & ExpRet = (
SELECT Max(ER_b.DATE) & Max(ER_b.ExpRet)
FROM ER ER_b
WHERE ER_b.DATE = ER.DATE
GROUP BY ER_b.DATE);
0
 

Author Comment

by:kobys
Comment Utility
I can get it to run now but the ranking is wrong.  In SQL View I have
 
SELECT t1.Date, t1.Ticker, t1.ExpRet, (select count(*) from Investable_Universe as t2 where t2.Date = t1.Date and t2.ID <= t1.ID) AS Rank, *
FROM Investable_Universe AS t1;

Open in new window


and my output for one month looks like (Expr1 is ExpRet):


Date	Ticker	Expr1	Rank
12/31/2006	RHD	-0.3356	53
12/31/2006	CTL	-0.3315	12
12/31/2006	XRX	-0.3286	77
12/31/2006	USM	-0.3264	69
12/31/2006	SEE	-0.3220	59
12/31/2006	AZO	-0.3193	5
12/31/2006	VIA/B	-0.3137	70
12/31/2006	TAP	-0.3053	64
12/31/2006	UHS	-0.3053	68
12/31/2006	SWY	-0.3046	63
12/31/2006	ARW	-0.3042	2
12/31/2006	CSC	-0.3040	11
12/31/2006	EDS	-0.3034	19
12/31/2006	PTV	-0.3033	51
12/31/2006	S	-0.3013	58
12/31/2006	RSG	-0.2995	57
12/31/2006	CTX	-0.2981	13
12/31/2006	FDC	-0.2962	21
12/31/2006	CYT	-0.2957	15
12/31/2006	LIZ	-0.2918	36
12/31/2006	LUV	-0.2901	38
12/31/2006	KR	-0.2901	34
12/31/2006	FO	-0.2897	22
12/31/2006	CMI	-0.2893	10
12/31/2006	CVH	-0.2862	14
12/31/2006	AVT	-0.2854	4
12/31/2006	ODP	-0.2831	49
12/31/2006	LZ	-0.2807	39
12/31/2006	MAR	-0.2801	41
12/31/2006	HMA	-0.2798	28
12/31/2006	LEN	-0.2774	35
12/31/2006	BLC	-0.2768	8
12/31/2006	YUM	-0.2758	79
12/31/2006	WM	-0.2750	75
12/31/2006	GCI	-0.2732	23
12/31/2006	M	-0.2714	40
12/31/2006	JCP	-0.2704	33
12/31/2006	R	-0.2687	52
12/31/2006	SLE	-0.2686	61
12/31/2006	GMT	-0.2671	24
12/31/2006	PHM	-0.2664	50
12/31/2006	JAVA	-0.2663	32
12/31/2006	HES	-0.2615	27
12/31/2006	HAS	-0.2596	26
12/31/2006	BDK	-0.2573	7
12/31/2006	NBR	-0.2571	45
12/31/2006	MWV	-0.2566	44
12/31/2006	HNZ	-0.2540	29
12/31/2006	RRD	-0.2538	56
12/31/2006	DRI	-0.2535	18
12/31/2006	TWX	-0.2525	66
12/31/2006	VLO	-0.2499	71
12/31/2006	BC	-0.2480	6
12/31/2006	EMN	-0.2478	20
12/31/2006	ASH	-0.2457	3
12/31/2006	NWL	-0.2438	47
12/31/2006	WY	-0.2420	76
12/31/2006	SHW	-0.2404	60
12/31/2006	IP	-0.2404	31
12/31/2006	MAS	-0.2402	42
12/31/2006	APC	-0.2395	1
12/31/2006	RPM	-0.2341	55
12/31/2006	NYT	-0.2330	48
12/31/2006	LTD	-0.2312	37
12/31/2006	WFT	-0.2304	72
12/31/2006	DHI	-0.2280	16
12/31/2006	XTO	-0.2227	78
12/31/2006	MAT	-0.2215	43
12/31/2006	SUN	-0.2193	62
12/31/2006	CMC	-0.2170	9
12/31/2006	IGT	-0.2165	30
12/31/2006	NVR	-0.2075	46
12/31/2006	RIG	-0.2039	54
12/31/2006	WHR	-0.2027	73
12/31/2006	TYC	-0.1691	67
12/31/2006	TIN	-0.1682	65
12/31/2006	GSF	-0.1323	25
12/31/2006	DO	-0.1251	17
12/31/2006	WIN	-0.1124	74

Open in new window

0
 

Author Closing Comment

by:kobys
Comment Utility
Thanks Sharath!  This worked perfectly after I figured out how to use it in SQL view instead of design view.
0
 

Author Comment

by:kobys
Comment Utility
Just had to make one small change so this was my final code:

 
SELECT t1.Date, t1.Ticker, t1.ExpRet, (select count(*) from Investable_Universe as t2 where t2.Date = t1.Date and t2.ExpRet <= t1.ExpRet) AS Rank, *
FROM Investable_Universe AS t1;

Open in new window

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now