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.
| Author |
Topic |
|
Pingu
Starting Member
2 Posts |
 Posted - 2005-06-12 : 11:31:44
|
| Hi. Please someone help...I have a database with passengers and trips! I want to know witch passenger travel more times...I have to count the number of trips witch one made and get the maximum of it... Query Analyzer doesn't allow tto do Max(Count(...)) |
|
|
Richard Branson
Yak Posting Veteran
84 Posts |
Posted - 2005-06-12 : 14:33:48
|
| Could you please give us more info and an idea of what your database looks likeYou can't teach an old mouse new clicks. |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-06-12 : 14:58:02
|
| Here's one way:select top 1 passenger, count(trips) as trips from myTable group by passenger order by 2 descBe One with the OptimizerTG |
|
|
|
Pingu
Starting Member
2 Posts |
Posted - 2005-06-12 : 19:50:06
|
| Thank you TGI don't try yet but I think that's the best way... |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2005-06-12 : 20:34:10
|
| select Passenger, MAX(TripCount)from (select p.Passenger, count(*) TripCount from Passengers p join Trips t on t.Passenger = p.Passenger group by p.Passenger) PassTripHTH=================================================================All restraints upon man's natural liberty, not necessary for the simple maintenance of justice, are of the nature of slavery, and differ from each other only in degree. -Lysander Spooner, lawyer (1808-1887) |
|
|
|
vivek.kumargupta
Starting Member
45 Posts |
Posted - 2005-06-13 : 03:09:47
|
| select passengers,max(tripstaken)from(select passengernames, count (trips) tripstakenfrom passengers pjoin trips t on p.passengers=t.passengers)t--hope this solves the problem .give a sample data for the same in any caseThanks, Vivek |
|
|
|
|
|
|
How to get the maximum of a count