March 27, 2002 at 7:43 am
well , I need rum this query:
select distinct month(date)as Month,year(date) as Year,siteid,callid,country,count(country) as totalcalls
from oldbill01 where country <> 'ITS' and siteid <> ' ' and country<> ' ' and Callid='Pax'
group by country,siteid,month(date),year(date),callid order by month,siteid,totalcalls desc
works good but I need only the top 10 countries for each vessel.This querie give me everything, I don't need that.
If I try select Top 10 I only get the first 10 lines,I don't want that either.
I need the top 10 for each vessel, we have around 100 veseels.
any sugestions ???
thanks
March 27, 2002 at 8:44 am
Sorry Nelson, maybe it's old-timers disease but what does your reference to vessel mean?
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
March 27, 2002 at 8:46 am
Vessel = siteid
means by ship (boat)
March 27, 2002 at 9:01 am
OK Got it. Now if there's a very clever SQL out there that will do this, I would like to know as much as you.
In the meantime a more pedestrian solution suggests itself to me and that is to use your select statment to build a Cursor inside a stored procedure. You can then step through the recordset checking for a change in Vessel ID. When you get one pop the next ten records out into a temporary (or not so temporary) table, carry on stepping through until the Vessel ID changes again.
That way you'll end up with a table containing the records you want, and a simple query on that table will give you the top 10 for each vessel.
If there's a better way, I'd love to learn it
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
March 27, 2002 at 10:07 am
Do you have any kind of primary key on the table or unique column with index?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 27, 2002 at 12:19 pm
Yes the primary Key is siteid
nothing more, no indexes
March 27, 2002 at 1:04 pm
NO NO, sorry there is no primary key and NO indexes
March 27, 2002 at 3:56 pm
I do have one working method that requires a field with unique data. I suggest add an Identity field and make the column a clustered indexed if you can then do something like the following
SELECT
month(date)as Month,year(date) as Year,siteid,callid,country,count(country) as totalcalls
FOM oldbill01 out1
WHERE IDFLD IN (select top 10 month(date)as Month,year(date) as Year,siteid,callid,country,count(country) as totalcalls
from oldbill01 in1 where country <> 'ITS' and siteid <> ' ' and country<> ' ' and Callid='Pax' AND in1.IDFLD = out1.IDFLD
order by month,siteid,totalcalls desc)
Now of course that is seat of my pants right now but should be correct with a few minor changes. This is the only way I see of getting what you want, if you cannot add an ID field then 1 How big is the table or at least the range you will work with? 2 can you use a temptable?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 1, 2002 at 1:12 am
My solution is along the same lines as antares686. You need a primary key for it to work. I think his solution is close, but I don't think it will work as is, because the subquery needs to return only countries, for the "in" operator to function.
Here's my query: substitute your PK for the "pkid"
select distinct
month(ob1.date)as Month,
year(ob1.date) as Year,
ob1.siteid,
ob1.callid,
ob1.country,
count(ob1.country) as totalcalls
from oldbill01 ob1
where ob1.country <> 'ITS'
and ob1.siteid <> ' '
and ob1.country<> ' '
and ob1.Callid='Pax'
group by ob1.country,
ob1.siteid,
month(ob1.date),
year(ob1.date),
ob1.callid
having ob1.country in
(select top 10 ob2.country
from oldbill01 ob2
where ob2.pkid = ob1.pkid
order by
ob2.month,
ob2.siteid,
ob2.totalcalls desc
)
order by ob1.month,ob1.siteid,ob1.totalcalls desc
BTW it strikes me as bizarre that you would not have a primary key on the table. Why is this?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply