Top X records from Top X list - 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

Transact-SQL (2000)

Top X records from Top X list

Author

Topic

paul27_uk
Starting Member

2 Posts

Posted - 2006-07-20 : 04:56:00

Hi, I was wondering if someone can help a newcomer in this field. I currently produce Top 10 Reports of where our staff travel, grouped on Location, this is also grouped on all the Hotels used. This could be anything from 1-100 different properties. I was wondering if there is something that can be done where I can produce a report that shows, on a specified period, the Top ten locations on all hotels, but then shows only the top 10 properties used in that location, with maybe the rest grouped as 'Others' (this bit isn't necessary).

Any feedback would be excellent, should you need more info, or I am unclear then please do not hesitate to drop me a mail.

Thanks again,

Paul

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-20 : 05:33:15

Can you post your table DDL, some sample data and the result that you want ?

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-20 : 05:46:32

something like this

select *
from tbl t
where loc in (select top 10 loc from tbl t2 group by loc order by count(*) desc)
and hotel in (select top 10 hotel from tbl t2 where t2.loc = t.loc group by hotel order by count(*) desc)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

paul27_uk
Starting Member

2 Posts

Posted - 2006-07-20 : 06:09:50

Hi, thanks fo the replies.

Hotel table
-Hotel Reference (pk)
-Location
...
Booking table
-Booking Refernce (pk)
-Hotel Reference (fk)
-Date of Booking
-Cost

All i want to see is
Location (1 of 10)
Hotel 1 - 10 (Hotel reference/Bookings/Cost)
Location (2 of 10)

Hotel 1 - 10 (Hotel reference/Bookings/Cost)

So in total I would expect to see 100 lines of data.

Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-20 : 06:21:48

something like
select *
from Hotel h
where h.Location in
(select top 10 Location from Hotel h join Booking b on h.href = b.href group by h.location order by count(*) desc)
and h.href in
(select top 10 h2.href from hotel h2 join Booking b on h.href = b.href where h.location = h2.location group by h2.href order by count(*) desc)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2006-07-20 : 09:17:42

something similar to this might give you what you need:

Select top 10
location, null as hotrelref, sum(bookings), sum(cost)
from hotel h
inner join bookings b
on b.hotelref = h.hotelref
group by 1,2

union

Select top 10
hr.location, h.hotelref, sum(bookings), sum(cost)
from hotel h
inner join (
Select top 10
location, sum(bookings), sum(cost)
from hotel h
inner join bookings b
on b.hotelref = h.hotelref
group by location)
on b.hotelref = h.hotelref
group by location) hr
on hr.location =h.location
group by hr.location, h.hotelref

order by 1,2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources