Ranking results... - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Ranking results...

Author

Topic

ramdas
Posting Yak Master

181 Posts

Posted - 2003-09-04 : 11:16:50

Hi folks,
I have the following situation:
A .000000 2003-07-01 00:00:00.000 1
B .000000 2003-07-01 00:00:00.000 2
D .000000 2003-06-02 00:00:00.000 52
E .000000 2003-06-02 00:00:00.000 45

How do I get the result set as
B .000000 2003-07-01 00:00:00.000 2
D .000000 2003-06-02 00:00:00.000 52

I am sure there is some ranking logic involved, could someone give me an insight.
Thank you in advance.

Ramdas

Ramdas Narayanan
SQL Server DBA

mr_mist
Grunnio

1870 Posts

Posted - 2003-09-04 : 11:19:33

If you provided column names then this would make more sense, but essentially you are after

SELECT
col1, col2, max (col3) as col3
FROM
yourtable
group by col1, col2

You may find results vary if your dates are not exact matches.

-------
Moo. :)

ramdas
Posting Yak Master

181 Posts

Posted - 2003-09-04 : 11:31:23

Hi,
The columns are employee,value,date and callcount. I need the results with the employee,value,date and the max of callcount for the particular day.
Emp value date maxcallcount
B .000000 2003-07-01 00:00:00.000 2
D .000000 2003-06-02 00:00:00.000 52

Ramdas

Ramdas Narayanan
SQL Server DBA

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-09-04 : 11:35:00

[code]
select
employee,
value,
date,
callcount
from
ramdas r
where
not exists (
select 1
from
ramdas
where
r.employee = employee and
r.date = date and
r.callcount < callcount)
[/code]

Jay White
{0}

ramdas
Posting Yak Master

181 Posts

Posted - 2003-09-04 : 11:42:24

Hi Jay,
I tried your query, but i am not getting the result set, I still get 2 records for a given day.

Thank you
Ramdas

Ramdas Narayanan
SQL Server DBA

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-09-04 : 12:47:19

See if this will work for you:

SELECT	a.Emp
	, a.value 
	, a.date
	, b.callcount

FROM	yourtable a JOIN
	(
	SELECT	date, max(callcount) 'callcount'
	FROM	yourtable
	GROUP BY date
	) b

	ON 	a.date = b.date
	AND	a.callcount = b.callcount

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources