Please help on SQL's Top and Max

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)

Please help on SQL's Top and Max

Author

Topic

travelforce
Starting Member

2 Posts

Posted - 2004-06-10 : 21:36:24

I have a table with the following :
depcity,arrivecity,farebasis
SYD,MEL,KIPOX
SYD,MEL,KIPOX
SYD,BNE,Y
SYD,MEL,Y
SYD,BNE,KIPOX,
SYD,BNE,HISOX
SYD,BNE,Y
.
.
.
.
I need a recordset that will give me with the following results:
The Top 5 route and the top farebasis for each route
where route = depcity+arrrivecity
e.g.
Route,Top1FareBasis,CountFareBasis
SYDMEL,KIPOX,2
SYDBNE,Y,2
.
.

Any help would be appreciated. Thanks

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-06-10 : 22:16:26

Probably simplest to break it down into bits:

Top 5 Routes:
select top 5 depcity + arrivecity, count(*)
from fares
group by depcity arrivecity
order by 2 Desc

Stay tuned for the other one....

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-06-10 : 22:34:35

Not as elegant as it could be, but here goes:

create view CommonRecs as

select depcity + arrivecity as Route, farebasis, count(*) as CountFB
from fares
group by depcity + arrivecity, farebasis

go

select f.depcity + f.arrivecity, f.farebasis, count(*)
from fares f
group by depcity + arrivecity, farebasis
having count(*) = (select max(countfb) from commonrecs where route = f.depcity + f.arrivecity)

travelforce
Starting Member

2 Posts

Posted - 2004-06-11 : 00:53:45

Thanks Timmy for your quick reply.
Is there a better way to do it without using the view? ie in a single query?
It just gets complicated for me once I throw in some parameters with a couple of joins to complete my query.

Thanks again.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-06-11 : 01:43:32

It's probably possible - I tried replacing the view name in the query with the view's query but I kept getting errors.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources