SELECT MIN question

[Mondeo]

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 i've 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

[PlausiblyDamp]

You could do

[highlight=SQL]

SELECT TOP 1 FROM Quote, ID Order BY Quote ASC

[/highlight]

[amir100]

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 i've 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

 

Hi again Mondeo. :D

 

What PlausiblyDamp said will indeed produce the result that you need. But here's an alternative. Maybe you'd like to consider using nested queries.

 

SELECT id, name, quote

FROM quotes

WHERE quote = (SELECT MIN( quote ) FROM quotes)

 

You'll have a broader view in query alternatives once you get a hang on to nested queries.

[Mondeo]

Thanks guys,

 

Okay, taking this example forward into production i've 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

[alreadyused]

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 you're wanting it to separate by the Car Type for example, you're going to probably have to work in a GROUP BY statement.

 

Sorry if i misunderstood and am off in outerspace... it happens a lot ;-)

[Mondeo]

Yes you're 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.

 

I'm 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

[alreadyused]

I've 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 "you're going to probably have to work in a GROUP BY" I really should have said "this is where the error is, and i'm GUESSING you need to work in GROUP BY." Sorry for any confusion on that, I'm not sure the Group By is the solution you need, I was just taking a stab at that.

 

Looking back at PD's suggestion earlier, you may need to work that in:

SELECT TOP 1 FROM Quote, ID ORDER BY Quote ASC

And again by saying "may" i'm just guessing.

 

I'll 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

[alreadyused]

ok you need to test this on your info, but here's what i've got; given a table "test" of:

"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:

SELECT id, make, model, MIN(price) FROM test GROUP BY make, model;

INCORRECTLY returns

"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:

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:

1,"honda","accord","2000"
7,"mazda","protege","999"

which is CORRECT! :-D

 

not sure if you've 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, I'm not sure. So hopefully someone with more SQL knowledge will provide some insight.