Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old February 20th, 2007, 03:25 PM
Authorized User
 
Join Date: Jun 2004
Location: York, East Yorkshire, United Kingdom.
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default MIN( MAX( UPDATE

Hi there all,

I have one I simply cannot get my head around, am I being daft or is it staring me in the face?

I'll start with the data:

tbl_1
sku rank price
abc 1 10.99
abc 2 11.99
abc 3 12.99
abc 3 13.99
xyz 1 10.99
xyz 2 11.99
xyz 3 12.99
xyz 3 13.99

tbl_2
sku [price] [min-price]
abc 11.50 x.xx
xyz 12.50 x.xx

I need to pick out the price from tbl_1 where it is greater than the price in tbl_2, but has the lowest rank and update that value into tbl_2 and of course has the same sku (tbl_1.sku = tbl_2.sku).

Nothing I try will seem to allow me to do it, I can push it into a new table with an INSERT query but, is there any reason why I cannot do this with an UPDATE?

Any help would be greatfully received.

Thanks so much in advance for your asistance.

Stuart
Reply With Quote
  #2 (permalink)  
Old February 20th, 2007, 03:28 PM
Authorized User
 
Join Date: Jun 2004
Location: York, East Yorkshire, United Kingdom.
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

tbl_1
sku rank price
abc 1 10.99
abc 2 11.99
abc 3 12.99
abc 4 13.99
xyz 1 10.99
xyz 2 11.99
xyz 3 12.99
xyz 4 13.99

tbl_2
sku [price] [min-price]
abc 11.50 x.xx
xyz 12.50 x.xx
Reply With Quote
  #3 (permalink)  
Old February 20th, 2007, 06:11 PM
Authorized User
 
Join Date: Oct 2005
Location: , , .
Posts: 72
Thanks: 0
Thanked 0 Times in 0 Posts
Default

This query will give you the lowest rank for each sku where the price in tbl_1 is greater than the price in tbl_2

SELECT sku, MIN(Rank) AS Rank
FROM tbl_1 INNER JOIN tbl_2 ON tbl_1.sku = tbl_2.sku AND
tbl_1.price >= tbl_2.price

Now to get the actual tbl_1 record, you need to join this back to tbl_1:

SELECT tbl_1.*
FROM tbl_1 INNER JOIN (SELECT sku, MIN(Rank) AS Rank
FROM tbl_1 INNER JOIN tbl_2 ON tbl_1.sku = tbl_2.sku AND
tbl_1.price >= tbl_2.price) A
ON tbl_1.sku = A.sku and tbl_1.rank = A.rank

SQL Server Helper
How well do you know SQL? Find out with the free test assessment from SQL Server Helper!!!
http://www.sql-server-helper.com/free-test/default.aspx

Got a SQL Server Question? Ask us here: http://www.sql-server-helper.com/forums/default.asp
Reply With Quote
  #4 (permalink)  
Old February 21st, 2007, 06:10 AM
Authorized User
 
Join Date: Jun 2004
Location: York, East Yorkshire, United Kingdom.
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you for your fast response..

The first query does the job with selecting the correct rank, but I need it to update that rank number into tbl_2.rank or the price into tbl_2.[min-price]

SELECT sku, MIN(Rank) AS Rank
FROM tbl_1 INNER JOIN tbl_2 ON tbl_1.sku = tbl_2.sku AND
tbl_1.price >= tbl_2.price

Either way I can work from there fairly easily.
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
How does one get the Max or Min Filename? crabjoe ASP.NET 1.0 and 1.1 Basics 4 January 15th, 2008 03:48 PM
MAX/MIN Value Help slbibs SQL Server 2005 4 November 6th, 2007 03:00 AM
Min / Max followup danbush XSLT 2 August 31st, 2005 12:02 PM
MAX and MIN ON TD cleytonjordan XSLT 7 July 27th, 2005 12:39 PM
SQL Max() and Min() Functions Help need... shana Access 1 September 7th, 2004 09:22 AM



All times are GMT -4. The time now is 04:15 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.