SELECT MIN - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SELECT MIN

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-07-11 : 07:49:56

Example table

Customer Price

SMITH 29.50
JONES 39.75
BRADLEY 10.89
MURPHY 12.78
JACKS 35.60
PETERSON 14.67
GORD 50.85
PAUL 50.90

I want to return the lowest price and the customer, so in this example my results should be

BRADLEY 10.89

I know I can use SELECT MIN (price) but how to I get the customer also

Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-11 : 07:54:16

maybe

select min(customer), min(price)
from YourTable

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-07-11 : 08:11:44

[code]Select Customer, Price
From Table
Where Price = (Select Min(Price) from Table)[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

b.veenings
Yak Posting Veteran

96 Posts

Posted - 2007-07-11 : 08:38:20

select customer, (min)price from example table
group by customer, price

Need an SQLDB consultant?
check www.veeningsengineering.nl

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-11 : 08:40:10

quote:
Originally posted by b.veenings

select customer, (min)price from example table
group by customer, price

Need an SQLDB consultant?
check www.veeningsengineering.nl

Adding price in the group by clause will give you different result

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-11 : 08:43:30

quote:
Originally posted by b.veenings

select customer, (min)price from example table
group by customer, price

Need an SQLDB consultant?
check www.veeningsengineering.nl

That would not give the min price and the customer. It basically will return all distinct combination of customer & price.

KH
[spoiler]Time is always against us[/spoiler]

b.veenings
Yak Posting Veteran

96 Posts

Posted - 2007-07-11 : 08:58:12

oops!

Need an SQLDB consultant?
check www.veeningsengineering.nl

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-11 : 08:59:10

well all our solutions are valid because we don't really know what the exact
requirement is based on the description.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

b.veenings
Yak Posting Veteran

96 Posts

Posted - 2007-07-11 : 09:07:30

dunno if this works but is worth a try.

select customer a, (min)price e from table
where e.price = a.customer

Need an SQLDB consultant?
check www.veeningsengineering.nl

b.veenings
Yak Posting Veteran

96 Posts

Posted - 2007-07-11 : 09:09:36

not really my day, forgot the inner join:-P

but i think the following one is the most appropiate:

[qoute]Select Customer, Price
From Table
Where Price = (Select Min(Price) from Table)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"[/qoute]

Need an SQLDB consultant?
check www.veeningsengineering.nl

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-11 : 09:35:33

quote:
Originally posted by b.veenings

dunno if this works but is worth a try.

select customer a, (min)price e from table
where e.price = a.customer

Need an SQLDB consultant?
check www.veeningsengineering.nl

I wonder if this type of syntax is available

Madhivanan

Failing to plan is Planning to fail

b.veenings
Yak Posting Veteran

96 Posts

Posted - 2007-07-11 : 09:41:38

what was in that coffee

Need an SQLDB consultant?
check www.veeningsengineering.nl

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-07-11 : 12:00:15

Thanks for suggestions,

I thought the most suitable one was

SELECT Customer FROM Table WHERE Price = (SELECT MIN(Price) FROM Table)

But how does SQL behave if two prices are both the MIN, for example if there were two different customers both with a price of 1.00?

b.veenings
Yak Posting Veteran

96 Posts

Posted - 2007-07-11 : 12:09:06

then you get them both back i suppose.

Need an SQLDB consultant?
check www.veeningsengineering.nl

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-07-12 : 05:01:29

In this scenario is there anyway to only return 1 record. Could I wrap the whole thing in a SELECT TOP 1 FROM ( ) Is that the best way?

b.veenings
Yak Posting Veteran

96 Posts

Posted - 2007-07-12 : 05:05:57

in that case you can choose to use top 1.

Need an SQLDB consultant?
check www.veeningsengineering.nl

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-12 : 05:08:34

quote:
Originally posted by Mondeo

In this scenario is there anyway to only return 1 record. Could I wrap the whole thing in a SELECT TOP 1 FROM ( ) Is that the best way?

If you only one 1 record


select top 1 * 
from   yourtable
order by price

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-12 : 05:29:59

quote:
Originally posted by khtan

quote:
Originally posted by Mondeo

In this scenario is there anyway to only return 1 record. Could I wrap the whole thing in a SELECT TOP 1 FROM ( ) Is that the best way?

If you want only one 1 record

select top 1 *
from yourtable
order by price

KH
[spoiler]Time is always against us[/spoiler]