Help with select statement - 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 2005 Forums

Transact-SQL (2005)

Help with select statement

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-06-19 : 19:09:12

Hi, i've run into a problem with a nested query. Consider this:

SELECT VehicleRef, Type, Manufacturer, Model, Derivative, isLimited, Term, MilesPA, CH FROM vwVehicles WHERE type='Car' AND manufacturer='Audi' AND model='A3'

I get the following results

1 Car Audi A3 1.6 Special Edition 3dr 0 2 10000 229.99
1 Car Audi A3 1.6 Special Edition 3dr 0 2 10000 252.99
1 Car Audi A3 1.6 Special Edition 3dr 0 3 10000 219.99
1 Car Audi A3 1.6 Special Edition 3dr 0 3 10000 242.99
1 Car Audi A3 1.6 Special Edition 3dr 0 2 20000 252.99
1 Car Audi A3 1.6 Special Edition 3dr 0 2 20000 296.99
1 Car Audi A3 1.6 Special Edition 3dr 0 3 20000 241.99
1 Car Audi A3 1.6 Special Edition 3dr 0 3 20000 291.99
1 Car Audi A3 1.6 Special Edition 3dr 0 2 30000 274.99
1 Car Audi A3 1.6 Special Edition 3dr 0 2 30000 340.99
1 Car Audi A3 1.6 Special Edition 3dr 0 3 30000 264.99
1 Car Audi A3 1.6 Special Edition 3dr 0 3 30000 339.99
2 Car Audi A3 1.6 Special Edition Sportback 5dr 0 2 10000 227.99
2 Car Audi A3 1.6 Special Edition Sportback 5dr 0 2 10000 249.99
2 Car Audi A3 1.6 Special Edition Sportback 5dr 0 3 10000 220.99
2 Car Audi A3 1.6 Special Edition Sportback 5dr 0 3 10000 244.99
2 Car Audi A3 1.6 Special Edition Sportback 5dr 0 2 20000 253.99
2 Car Audi A3 1.6 Special Edition Sportback 5dr 0 2 20000 298.99
2 Car Audi A3 1.6 Special Edition Sportback 5dr 0 3 20000 244.99
2 Car Audi A3 1.6 Special Edition Sportback 5dr 0 3 20000 292.99
2 Car Audi A3 1.6 Special Edition Sportback 5dr 0 2 30000 279.99
2 Car Audi A3 1.6 Special Edition Sportback 5dr 0 2 30000 344.99
2 Car Audi A3 1.6 Special Edition Sportback 5dr 0 3 30000 267.99
2 Car Audi A3 1.6 Special Edition Sportback 5dr 0 3 30000 339.99

What I need is to only retrieve each vehicleID once, but with the best (lowest) CH price, I also need the term and mileage that corresponds with that price.

I tried this

SELECT VehicleRef, Type, Manufacturer, Model, Derivative, isLimited, Term, MilesPA, CH FROM vwVehicles WHERE type='Car' AND manufacturer='Audi' AND model='A3' AND CH = (SELECT MIN(CH) FROM vwVehicles WHERE type='Car' AND manufacturer='Audi' AND model='A3' AND ch >=1 AND ch <=1000)

This only returns 1 line though. Is there anyway to get the result I want using one query?

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-19 : 19:13:42

[code]
SELECT VehicleRef, Type, Manufacturer, Model, Derivative, isLimited, Term, MilesPA, CH
FROM vwVehicles v
WHERE type = 'Car'
AND manufacturer = 'Audi'
AND model = 'A3'
AND CH = (
SELECT MIN(CH)
FROM vwVehicles x
WHERE x.VehicleRef = v.VehicleRef
AND x.ch >= 1
AND x.ch <= 1000
)[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-19 : 19:27:13

Or learn the new SQL Server 2005 T-SQL functions and commands...

SELECT		d.VehicleRef,
		d.Type,
		d.Manufacturer
		d.Model,
		d.Derivative,
		d.isLimited,
		d.Term,
		d.MilesPA,
		d.CH
FROM		(
			SELECT		VehicleRef,
					Type,
					Manufacturer
					Model,
					Derivative,
					isLimited,
					Term,
					MilesPA,
					CH,
					ROW_NUMBER() OVER (PARTITION BY Type, Manufacturer, Model ORDER BY CH) AS RecID
			FROM		vwVehicles
		) AS d
WHERE		d.Type = 'Car'
		AND d.Manufacturer = 'Audi'
		AND d.Model = 'A3'
		AND d.RecID = 1

Peter Larsson
Helsingborg, Sweden

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-06-28 : 06:16:40

Thanks for this!

I have used this statement

SELECT VehicleRef, Type, Manufacturer, Model, Derivative, isLimited, Term, MilesPA, CH
FROM vwVehicles v
WHERE type = 'Car'
AND manufacturer = 'Audi'
AND model = 'A3'
AND CH = (
SELECT MIN(CH)
FROM vwVehicles x
WHERE x.VehicleRef = v.VehicleRef
AND x.ch >= 1
AND x.ch <= 1000
)

It returns 432 rows, BUT there are only 428 unique vehicle references in the table.

Also there is another column called PCH, if I change the statement and replace all the CH with PCH to get the lowest PCH prices it returns 462 rows.

Where have a gone wrong?

Thanks

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-06-28 : 06:29:46

May be you have more than one record which has the lowest price.

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-06-28 : 06:49:04

Can I get around this and just choose one

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-06-28 : 06:51:44

Since you need to select other fields with the lowest price,
you have to decide how to choose which record with the lowest price.

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-06-28 : 07:40:07

It doesn't matter, any, or the first one returned perhaps?

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-06-28 : 18:43:41

SELECT MIN(CH)
FROM vwVehicles x
WHERE x.VehicleRef = v.VehicleRef
AND x.ch >= 1
AND x.ch <= 1000

If I wrap that inner statment in this one would it work?

SELECT TOP 1 ()

Thanks

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-06-28 : 22:31:22

What column combination makes your records unique?

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-07-01 : 19:12:01

Hi,

I'm willing to pay someone on a consultancy basis to get this to work, my deadline is middle of week so panicking a bit now!

If your interested please drop me a line ben at chillilime dot com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources