asked on

Ranking by Group

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!

kobys

ASKER

I should add that I'm putting this line in the Field: part of the query in design view.

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kobys

ASKER

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

Guy Hengel [angelIII / a3]

please clarify what database you are using!

anyhow, please see this generic article that should solve your problem:
https://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:
https://www.experts-exchange.com/A_1499.html

hope this helps

Sharath S

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

ssisworo

try this :

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

ssisworo

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);

kobys

ASKER

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

kobys

ASKER

Thanks Sharath! This worked perfectly after I figured out how to use it in SQL view instead of design view.

kobys

ASKER

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