SELECT (similar items by price)

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)

SELECT (similar items by price)

Author

Topic

petem
Starting Member

44 Posts

Posted - 2004-10-01 : 05:48:33

Hi all,

I'm guessing that this is quite a common question but couldnt find anything googling etc.

I need one of these pages which shows similar items to the one chosen by the user. Example: a car is chosen that costs £10,995.00.

How can I show (say 3) other examples of cars at similar prices. Don't know how you would do it - ie: by 10% either way...? anyway this type of query,

any help much appreciated,

cheers.

Pete

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-10-01 : 05:51:33

select car from tablecar
where price=(select price from tablecar
where car=chosencar)

this is assuming car is unique?

haven't tested this though...

petem
Starting Member

44 Posts

Posted - 2004-10-01 : 05:54:57

Hey Jen. That was quick!!

Sorry, should have said. Im using a stored Proc so I have:

SELECT car FROM tablecar WHERE price = @price

The cars are not neccessarily unique either.

I'm guessing I need something Like

SELECT car FROM tableCar WHERE price BETWEEN (@PRICE -10%) AND (@PRICE +10%) - Obvioulsy with proper syntax though!

Pete

petem
Starting Member

44 Posts

Posted - 2004-10-01 : 06:00:53

OK,
I now have

WHERE price BETWEEN (@price * 0.9) AND (@price * 1.1)

Think that works!

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2004-10-01 : 06:02:13

You're almost there.


SELECT car
FROM tablecar 
WHERE price BETWEEN @price * .9 AND @price * 1.1

Mark

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2004-10-01 : 06:03:41

You just beat me to the punch Pete!

Mark

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-10-01 : 06:05:48

lolz, it's my hell day, can't sleep again, but my eyes are getting tired, luckily i'll have my dream sleep today.

so your user will choose the price range or the car? just compute for the percentage...

SELECT car FROM tableCar WHERE price BETWEEN (@PRICE-(@price*.1)) AND (@PRICE + (@price*.1))

petem
Starting Member

44 Posts

Posted - 2004-10-01 : 06:09:11

hey thanks guys, need to wake up myself - what the heck it's Firday

:)

petem
Starting Member

44 Posts

Posted - 2004-10-01 : 06:37:55

OK, this is a bit trickier.

After using this I realised that obviously sometimes there wont be 3 other items within (say 10%) of each car. Also it would be nice to maybe get a spread of prices: ie: one less expensive, one more expensive. Hence I now have:

SELECT top 1 * FROM tinsWebsiteSpecialOffers
WHERE price < @price
AND vehicleType != 'Commercial'
ORDER BY Price desc

SELECT top 1 * FROM tinsWebsiteSpecialOffers
WHERE price = @price
AND vehicleType != 'Commercial'

SELECT top 1 * FROM tinsWebsiteSpecialOffers
WHERE price > @price
AND vehicleType != 'Commercial'
ORDER BY Price asc

This gets the next least expensive, one the same, and the next most expensive. Again this works fine if these cars exist.

However, if the user picks the cheapest car (cos they're really stingy) then the first two items wont show anything....I guess if this happens then I want to show the next 3 least expensive if you get what I mean. Same goes for the most expensive car.

any ideas? cheers,

Pete

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-10-01 : 15:54:24

You could declare some variables and count how many records you find expensier and/or cheaper and make the adjustment to the Top statements depending on the counts you did previously

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-10-01 : 16:25:39

[code]Declare @price money
--Actual count
Declare @lessCount tinyint
Declare @sameCount tinyint
Declare @greaterCount tinyint
--Required count
Declare @lessReqCount tinyint
Declare @sameReqCount tinyint
Declare @greaterReqCount tinyint

SELECT @lessCount=count(*) FROM tinsWebsiteSpecialOffers
WHERE price < @price
AND vehicleType != 'Commercial'

SELECT @sameCount=count(*) FROM tinsWebsiteSpecialOffers
WHERE price = @price
AND vehicleType != 'Commercial'

SELECT @greaterCount=count(*) FROM tinsWebsiteSpecialOffers
WHERE price > @price
AND vehicleType != 'Commercial'

--minimum record selection = 5
Set @lessReqCount = 5
Set @sameReqCount = 5
Set @greaterReqCount = 5

if @greaterCount < @greaterReqCount
Set @lessReqCount = @lessReqCount + @greaterReqCount - @greaterCount

if @lessCount < @lessReqCount
Set @sameReqCount = @sameReqCount + @lessReqCount - @lessCount

if @sameCount < @sameReqCount
Set @greaterReqCount = @greaterReqCount + @sameReqCount - @sameCount

--I guess you would have to use dynamic SQL statements in this aproach[/code]

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

petem
Starting Member

44 Posts

Posted - 2004-10-04 : 03:39:42

Hey, cheers, (been away for the weekend with no access to web)

I'll get on this today.

Pete

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources