Display items less than parameter value? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2005)

Display items less than parameter value?

Author

Topic

BrettD
Starting Member

14 Posts

Posted - 2007-07-10 : 09:43:29

Hi,

I have the following procedure:

use [travelweboffers]
go
/****** object: storedprocedure [dbo].[sp_top18bargains] script date: 07/10/2007 14:23:53 ******/
set ansi_nulls on
go
set quoted_identifier on
go
alter procedure [dbo].[sp_top18bargains]

as

declare @resort varchar(100);
declare @appearances int
set @appearances = 3

delete from cb_top18bargains

/* distinct destinations */

declare curresort cursor for

select distinct destair
from cb_offers
where destair is not null
order by destair;

open curresort;

/* end dist dest. */

/* for each loop */

fetch next from curresort into @resort; -- fetch next destination

while (@@fetch_status = 0)

begin

insert into cb_top18bargains

select * from (

select distinct top 18 depair, destair, resort, depdate, price as minadultpricepp, rating, nights, travelweblive.dbo.cb_airport.name as deptname
from cb_offers left join travelweblive.dbo.cb_airport on cb_offers.depair = travelweblive.dbo.cb_airport.code
where price = ( select min(a.price)
from cb_offers as a
where a.destair = cb_offers.destair
and a.resort= cb_offers.resort )
and cb_offers.destair = @resort
group by depair, destair, resort, depdate, price, rating, nights, travelweblive.dbo.cb_airport.name
order by price asc
) tbl

where tbl.resort in (select resort from cb_resort where destinationiata = tbl.destair)

fetch next from curresort into @resort;

end

/* end loop */

close curresort;

deallocate curresort;

The results are shown on my web page calling the following SQL:
SELECT DISTINCT TOP 18 * from cb_top18Bargains order by MinAdultPricePP

What I want to do is only display the returned items in less occurences that my @appearances parameter in the SP.

How do I modify my SP to bring back each city (in this case) less than 3 times (as set for @appearance)?

Brett

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-07-11 : 04:23:34

Not sure where city comes into it, but if I understand you correctly you are saying
"I have some data that includes a city code and I want to select it but at most list only 3 of each city".
The dreaded "I don't care which one" routine.....Choose your order of preference in the <whatever> clause below.
This might get you there:

select * from
(select a,b,c, row_number() over(partition by city order by <whatever>) as cityCount from mytable
) mydata
where
cityCount<@appearances
order by MinAdultPricePP

Let me know how you go.

On a side note, you have way to many cursors, distincts and order bys. You can and should rewrite without them.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources