SQL quickie

[reds4ever]

how do i find the minimum value of a column based on another column?

ie If I had columns 'age' and 'height', how would I get the min (height) for (say) ages 9,12,15 ommiting all the other ages?

cheers

[Asher]

SELECT MIN(height) FROM table WHERE age=9 OR age=12 OR age=15

it might need to be...

SELECT MIN(height) AS MinHeight FROM table WHERE age=9 OR age=12 OR age=15

[reds4ever]

many thanks Asher

[Asher]

No problem. I'm dealing with programming SQL on a daily basis now so it was pretty easy to answer.

[reds4ever]

you got that job you said you had the second interview for a while back?

[Asher]

Yeah.

Sole programmer for a small business, making an app that calculates and spits out building instructions (they make stairs), maintains a DB of the customers, workorders, and stair models, integrates with Simply Accounting (invoices, etc) and AutoCAD (designs and models).

It's great fun. Great pay, too. And zero supervision.

[JohnT]

Congrats, Asher!

[reds4ever]

Originally posted by Asher
It's great fun. Great pay, too. And zero supervision.

The 3 'musts' IMHO!

Nice One!

[MattyBoy]

SELECT MIN(height) FROM table WHERE age in (9,12,15)

gives the same result and in most circumstances runs quicker (only matters if it is running slowly, of course).

[Urban Ranger]

Originally posted by Asher
Sole programmer for a small business, making an app that calculates and spits out building instructions (they make stairs), maintains a DB of the customers, workorders, and stair models, integrates with Simply Accounting (invoices, etc) and AutoCAD (designs and models).

Integrating with AutoCAD? With SQL?

[Asher]

No, the program just needs to integrate with AutoCAD so he can import blueprints into the program. SQL would only save the filenames of the blueprints.

Simply Accounting is wonderful to work with -- it actually stores all its information in an Access 2000 DB. All I needed to do was toss together a MS Jet OLE DB consumer (via the wizards, took seconds), and I'm putzing around inside all the data that I need with an SQL connection.