select similar (but bit trickier)

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 (but bit trickier)

Author

Topic

petem
Starting Member

44 Posts

Posted - 2004-10-01 : 07:00:31

After using the percent code 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 have:

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

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

SELECT top 1 * FROM cars
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. - Also, if there are no other cars of the SAME price then this wont show either.

any ideas? cheers,

Pete

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-10-01 : 08:50:30

pick upto 3 cars for each of the 3 conditions.
put the results together (union)
discard any duplicates
then look at the remainder and decide which entries you need.

this way you should cover all the bases.

petem
Starting Member

44 Posts

Posted - 2004-10-01 : 09:51:16

OK sounds like a plan......hmmmmm...any syntax help for this - sorry SQL is not my strong point (just trying to figure out what actually is!)

but I have something to go on for the weekend anyway... :0)

Pete

petem
Starting Member

44 Posts

Posted - 2004-10-01 : 10:53:32

Hi,

I can't use UNION as the first and third statements require ordering differently.

any ideas how I can run these three statements, put all the answers into one set, then perform the duplication check?

:)

Pete

X002548
Not Just a Number

15586 Posts

Posted - 2004-10-01 : 13:59:29

The order you want and no duplicates


SELECT * FROM (
  SELECT TOP 1 * 
    FROM cars
   WHERE price < @price
     AND vehicleType != 'Commercial'
ORDER BY Price DESC
			) AS A
UNION
SELECT * FROM (
  SELECT TOP 1 * 
    FROM cars
   WHERE price = @price
     AND vehicleType != 'Commercial'
--What? Nor ORDER BY Here?
			) AS B
UNION
SELECT * FROM (
  SELECT TOP 1 * 
   WHERE price > @price
     AND vehicleType != 'Commercial'
ORDER BY Price ASC
			) AS C

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources