SELECT MIN question

M

Mondeo

Well-known member
Joined
Nov 10, 2006
Messages
128
Location
Sunny Lancashire
I have this simple table

ID Name Quote
1 Smith 244.67
2 Jones 178.89
3 John 212.56

I need to select the lowest quote, so ive got

SELECT MIN(quote) FROM quotes

Which returns 178.89 as expected.

BUT...

I also need the record ID and customer name

SELECT MIN(quote),id,name throws an error.

How can I do this

Thanks
 
Mondeo said:
I have this simple table

ID Name Quote
1 Smith 244.67
2 Jones 178.89
3 John 212.56

I need to select the lowest quote, so ive got

SELECT MIN(quote) FROM quotes

Which returns 178.89 as expected.

BUT...

I also need the record ID and customer name

SELECT MIN(quote),id,name throws an error.

How can I do this

Thanks
Click to expand...

Hi again Mondeo. :D

What PlausiblyDamp said will indeed produce the result that you need. But heres an alternative. Maybe youd like to consider using nested queries.

SELECT id, name, quote
FROM quotes
WHERE quote = (SELECT MIN( quote ) FROM quotes)

Youll have a broader view in query alternatives once you get a hang on to nested queries.
 
Thanks guys,

Okay, taking this example forward into production ive run into a problem with 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 the best CH price, so I changed my query to this following Amirs advice

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 ??? What have I done wrong.

Thanks
 
if i understand correctly, you want the lowest value for EACH type of vehicle, not the absolute lowest for all, correct?

based on that understanding, the problem is this: your statement says
SELECT certain fields WHERE they meet all criteria

Part of your criteria is you say
"AND CH=MIN(more criteria)"
that results in ONE row that is the minimum in your table, so that means the first/outer select is saying one of the criteria is that it must be the minimum row in the table.

So, in other words it is correctly returning the row for absolute lowest priced car that meets all of your criteria.

If youre wanting it to separate by the Car Type for example, youre going to probably have to work in a GROUP BY statement.

Sorry if i misunderstood and am off in outerspace... it happens a lot ;-)
 
Yes youre correct. I need the lowest CH price for each type of vehicle, or each distinct vehicleref. I also need to know in the results the term and milespa that applies to the lowest CH price.

Im really stuck with this, spent ages on it with no luck. If you could help me with the GROUP BY approach it would be much appreciated!

Thanks
 
Ive been dabbling in programming for a while, so I know that spot is where the error is at because I understand the logic of the query there.

However, I am not very experienced at SQL, so yesterday when I said "youre going to probably have to work in a GROUP BY" I really should have said "this is where the error is, and im GUESSING you need to work in GROUP BY." Sorry for any confusion on that, Im not sure the Group By is the solution you need, I was just taking a stab at that.

Looking back at PDs suggestion earlier, you may need to work that in:
Plausibly Damp said:
SELECT TOP 1 FROM Quote, ID ORDER BY Quote ASC
Click to expand...
And again by saying "may" im just guessing.

Ill play around with it a bit today and see if I can come up with anything.

MODS please jump in if you have a suggestion! :-D
 
ok you need to test this on your info, but heres what ive got; given a table "test" of:
Code: 
"id","make","model","price"
1,"honda","accord","2000"
2,"honda","accord","2001"
3,"honda","accord","2002"
4,"mazda","protege","1000"
5,"mazda","protege","1001"
6,"mazda","protege","1002"
7,"mazda","protege","999"
notice i intentionally made row 7 the cheapest of the proteges..


running the following command:
Code: 
SELECT id, make, model, MIN(price) FROM test GROUP BY make, model;
INCORRECTLY returns
Code: 
"id","make","model","MIN(listPrice)"
1,"honda","accord","2000"
4,"mazda","protege","999"
Note that the ID 4 does not match with the price "999"

However, modifying that to:
Code: 
SELECT id, price, make, model FROM test a where price=(SELECT min(price) FROM test b where a.make=b.make and a.model=b.model) GROUP BY make, model;
now returns:
Code: 
1,"honda","accord","2000"
7,"mazda","protege","999"
which is CORRECT! :-D

not sure if youve done this before or not, so thing to note is that I am assigning the result of the first SELECT stmt to the var "a" and the second(nested) SELECT stmt to var "b".

The Group By statement is saying I only want one combo of the make, model... similar to the Distinct command, however it allows me to select any fields for viewing and filter down by a couple fields.

One last note, there is probably an easier way or more effecient way than this, Im not sure. So hopefully someone with more SQL knowledge will provide some insight.
 
Back
Top