Select Query By MIN() Value? - 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

General SQL Server Forums

New to SQL Server Programming

Select Query By MIN() Value?

Author

Topic

paramu
Posting Yak Master

151 Posts

Posted - 2009-07-28 : 06:58:25

I have 2-tables [1.mprmst 2.quotemst]
mprmst :
mpr_no itm_code itm_qty
10 ABC1 100
10 FGH2 20
11 ABC3 15
12 AGH1 25
quotemst:
item_code unit_price
ABC1 15
ABC2 16
ABC1 12
FGH2 17
ABC1 14.50

Now I want to select the minimum value of unitprice for a particular mpr_no.

I tried the below, showing error

select mprmst.itm_code,mprmst.itm_qty,min(qotemst.unit_price) as unit_rate from mprmst,qotemst where mprmst.mpr_no='" & Sel_Mpr & "' and qotemst.item_code=mprmst.itm_code

Paramu @ PARANTHAMAN

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-28 : 06:59:09

are you using SQL 2000 or 2005/2008 ?

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

paramu
Posting Yak Master

151 Posts

Posted - 2009-07-28 : 07:12:01

sqlserver 2008

Paramu @ PARANTHAMAN

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-07-28 : 07:24:55

You are missing the group by clause for other fields
select mprmst.itm_code,mprmst.itm_qty,min(qotemst.unit_price) as unit_rate from mprmst,qotemst where mprmst.mpr_no='" & Sel_Mpr & "' and qotemst.item_code=mprmst.itm_code
group by mprmst.itm_code,mprmst.itm_qty

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-28 : 08:32:41

also see http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

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

paramu
Posting Yak Master

151 Posts

Posted - 2009-07-28 : 09:02:49

NICE GUIDENCES!!!!!!
THANKS!!!

Paramu @ PARANTHAMAN

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources