SQL Question: Getting the row of min and max only

[jamesave]

suppose that I have a table with the following data:

Item Price
Apple $1.50
Orange $1.75
Kiwi $2.00
Tomato $0.99

What would be the correct SQL command to display the item with the max and min price at the same time?

TIA

 

[tfinch2]

First result on Google:

http://www.xaprb.com/blog/2006...-row-per-group-in-sql/

 

[KLin]

a union query.

SELECT Item, Max(Price) as ItemPrice from Table1 Group By Item
UNION
SELECT Item, min(Price) as ItemPrice from Table1 Group By Item

 

[Hyperblaze]

SELECT max(Price) as maxPrice, min(Price) as minPrice
FROM Table1

(mind you, I have not tested this out, but it should work)

 

[Markbnj]

Originally posted by: Hyperblaze
SELECT max(Price) as maxPrice, min(Price) as minPrice
FROM Table1

(mind you, I have not tested this out, but it should work)

I think it's actually...

SELECT maxPrice AS Max(Price), minPrice AS Min(Price) ... etc.

I considered suggesting this too, but it's hard to tell what it is the OP really needs to do. This query will get you the two numbers, but not the item names. For that the union query suggested by KLin is the way to go (but of course returns two rows, and who knows what the OP meant by "at the same time"), but I haven't quite figured out why the 'group by' is necessary.

As a general note to all young programmers, and even the not so young ones: we're in a technical business, and precision of language and description is not elective. I'm continually amazed by how vaguely people will describe programming challenges and problems when asking for help.

 

[Hyperblaze]

Originally posted by: Markbnj

Originally posted by: Hyperblaze
SELECT max(Price) as maxPrice, min(Price) as minPrice
FROM Table1

(mind you, I have not tested this out, but it should work)

I think it's actually...

SELECT maxPrice AS Max(Price), minPrice AS Min(Price) ... etc.

I considered suggesting this too, but it's hard to tell what it is the OP really needs to do. This query will get you the two numbers, but not the item names. For that the union query suggested by KLin is the way to go (but of course returns two rows, and who knows what the OP meant by "at the same time"), but I haven't quite figured out why the 'group by' is necessary.

As a general note to all young programmers, and even the not so young ones: we're in a technical business, and precision of language and description is not elective. I'm continually amazed by how vaguely people will describe programming challenges and problems when asking for help.

If you type this....

SELECT maxPrice AS Max(Price), minPrice AS Min(Price) ... etc.

You'll be getting the maxPrice field labelled as "Max(Price)"

The way I have it, it finds the max price and min price and labells the field without any special characters

 

[Markbnj]

Originally posted by: Hyperblaze

Originally posted by: Markbnj

Originally posted by: Hyperblaze
SELECT max(Price) as maxPrice, min(Price) as minPrice
FROM Table1

(mind you, I have not tested this out, but it should work)

I think it's actually...

SELECT maxPrice AS Max(Price), minPrice AS Min(Price) ... etc.

I considered suggesting this too, but it's hard to tell what it is the OP really needs to do. This query will get you the two numbers, but not the item names. For that the union query suggested by KLin is the way to go (but of course returns two rows, and who knows what the OP meant by "at the same time"), but I haven't quite figured out why the 'group by' is necessary.

As a general note to all young programmers, and even the not so young ones: we're in a technical business, and precision of language and description is not elective. I'm continually amazed by how vaguely people will describe programming challenges and problems when asking for help.

If you type this....

SELECT maxPrice AS Max(Price), minPrice AS Min(Price) ... etc.

You'll be getting the maxPrice field labelled as "Max(Price)"

The way I have it, it finds the max price and min price and labells the field without any special characters

Yep, you're right. Your original ordering of the terms was correct.

 

[KLin]

Actually my solution does not work . Try this

Select tblMax.Item, MaxPrice, tblMin.Item, MinPrice
FROM
(SELECT Top 1 Item, Price As MaxPrice from Table1 Order by Price DESC) tblMax,
(SELECT Top 1 Item, Price As MinPrice from Table1 Order By Price ASC) tblMin