Using the MIN function across fields in a record

[Guest]

I have a table with 50 records with 4 fields.
(Item, price1, price2, price3, )
neither of the fields is the lowest price, and I want to calculate the
lowest price for each item, I dont know how

 

[Duane Hookom]

I would normalize the table with a union query
SELECT Item, 1 as PriceNum, [Price1] as Price
FROM tblPrices
UNION ALL
SELECT Item, 2, [Price2]
FROM tblPrices
UNION ALL
SELECT Item, 3, [Price3]
FROM tblPrices;

You can then create a group by query based on your union query:

SELECT Item, Min(Price) as MinPrice
FROM quniYourNormalizedPrices;

 

[MGFoster]

I have a table with 50 records with 4 fields.
(Item, price1, price2, price3, )
neither of the fields is the lowest price, and I want to calculate the
lowest price for each item, I dont know how

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Design consideration: Your table is not normalized. Ideally, it should
be like this:

CREATE TABLE Prices (
Item integer not null references Items ,
price_number byte not null ,
price currency not null ,
CONSTRAINT PK_Prices PRIMARY KEY (Item, price_number)
)

Then you can get the MIN of each Item by doing this:

SELECT Item, Min(Price) As MinPrice
FROM Prices
WHERE < ... criteria ... >
GROUP BY Item

To get the lowest price in the table:

SELECT Item, Price
FROM Prices
WHERE price = (SELECT MIN(Price) FROM Prices)

Which may give you more than one Item if their lowest prices are the
same.

But the way your table is currently designed you have to do this:

SELECT Item,
IIf(price1 < price2 and price1 < price3, price1,
IIf(price2 < price1 and price2 < price3, price2,
IIf(price3 < price1 and price3 < price2, price3))) As lowest_price

FROM ...

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQiOchoechKqOuFEgEQIOEQCg31HlQE4DRMsqsJrbvOqSr7gGUrQAniqB
rSxR7SQ75vnmIT141s5LUw/1
=Cdog
-----END PGP SIGNATURE-----

 

[Duane Hookom]

MG,
You do a very thorough job of answering questions.

Do you have a utility to script your tables with constraints etc?
Or, do you whip this stuff off like a hot knife through butter?
Or, do you have more time to create replies than most of us?

Chris2 also does a very similar and thorough job of answering. You aren't
the same person are you? Come to think of it, I don't recall seeing you both
in the same thread at the same time...

Again, thanks for being so complete with your replies.

 

[MGFoster]

MG,
You do a very thorough job of answering questions.

Do you have a utility to script your tables with constraints etc?
Or, do you whip this stuff off like a hot knife through butter?
Or, do you have more time to create replies than most of us?

Chris2 also does a very similar and thorough job of answering. You aren't
the same person are you? Come to think of it, I don't recall seeing you both
in the same thread at the same time...

Again, thanks for being so complete with your replies.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Duane,
Thanks. I usually try to put myself in the place of the poster & then
try to give the answer I'd want: examples & explanations of the example.
Sometimes I just give an uncommented answer 'cuz I believe it is a
thorough answer (or I'm just tired ;-) ).

I also lurk/post in microsoft.public.sqlserver.programming and they
demand that posters put in a DDL of the tables they are working w/, some
sample data in INSERT statements & the complete query SQL. I believe
I've seen Chris2, or someone w/ the same handle, there.

I got used to making the CREATE TABLE definitions on the fly....though I
don't just snap it out, it takes a few minutes. And, yeah, I do tend to
have more free time than I'd like right now.

I also find your responses enlightening.
Cheers,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQiPOE4echKqOuFEgEQLk3wCfYvJLR8fPizpeUzVyb9lzG/PNeLIAoOWN
xNBJ6hYO9K9yMh63wlpmgrR1
=vJfI
-----END PGP SIGNATURE-----

 

[Chris2]

MG,
You do a very thorough job of answering questions.

Do you have a utility to script your tables with constraints etc?
Or, do you whip this stuff off like a hot knife through butter?
Or, do you have more time to create replies than most of us?

Chris2 also does a very similar and thorough job of answering. You aren't
the same person are you? Come to think of it, I don't recall seeing you both
in the same thread at the same time...

Duane,



No, we are not the same person. I just type 80 words per minute, and
have memorized the basic syntax of CREATE TABLE. Typing it over and
over again for answers. . . you just get used to it. Of course, now
and then people get my "give me the DDL" response, when I just can't
figure it out and don't want to pick apart the question for
particulars in an endless repitition of posts when I know the DDL,
Data, and Expected Output will solve more of my questions than
anything else.

Again, thanks for being so complete with your replies.

And again, thank you for the compliment! That's highly
appreciated. Although as I recall telling someone a few weeks ago,
there are people around here who have forgotten more about MS Access
than I'll ever know.

 

[Chris2]

Duane,
Thanks. I usually try to put myself in the place of the poster & then
try to give the answer I'd want: examples & explanations of the example.
Sometimes I just give an uncommented answer 'cuz I believe it is a
thorough answer (or I'm just tired ;-) ).

I also lurk/post in microsoft.public.sqlserver.programming and they
demand that posters put in a DDL of the tables they are working w/, some
sample data in INSERT statements & the complete query SQL. I believe
I've seen Chris2, or someone w/ the same handle, there.

It's me. It's been a *huge* time since I actively posted on
microsoft.public.sqlserver.progamming (in 1999-2001 I posted quite a
bit), but now that I have a desktop copy again on my personal machine
(SQL Server 2005 Express December CTP (Beta 3)), I've ventured a
couple of posts there because now I can test my answers out.


Sincerely,

Chris O.

 

[Chris2]

Duane Hookom wrote:

I also lurk/post in microsoft.public.sqlserver.programming and they
demand that posters put in a DDL of the tables they are working w/, some
sample data in INSERT statements & the complete query SQL.

MGFoster,

Yup, they're pretty draconian over there. Getting a response from Mr.
Celko can be quite an experience, too. However, given the ease with
which a simple laid-out T-SQL script can create all tables and load
them with data, ready made for a volunteer to use the important part
of their minimal time and available brainpower on, it just makes sense
for it to be a basic requirement (not that lots of people don't ignore
it).

It isn't so easy with Access, as it largely provides no easy way (not
even VBA) of doing what T-SQL can. You can make a quick table via the
GUI, but have no DDL. Worse, if you make a big complicated table in
the GUI, and accidentally delete that table before you back up that
day . . . oops, you have to painstakingly recreate it by hand.

Personaly, I only use the GUI rarely for Table or Query design. I
just pound out the DDL/DML, and save away the Queries. I find it to
be far more useful to have a Drop and Create Query than laboriously go
through the GUI each time. Although there are a few bits here and
there that Access will let you do, that JET's CREATE isn't so good it.

Access' Design View for Tables has long lacked a "Script DDL" function
to output a simple printed listed of the SQL involved. (At least it
isn't there in MS Access 2000.)


Sincerely,

Chris O.