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.
| Author |
Topic |
|
deadfish
Starting Member
38 Posts |
 Posted - 2003-11-13 : 22:55:33
|
| I have a table that I have to select the last record regarding to some selection criteria, for examplefor tablecust_no item_no qty effect_date price001 item1 10 2003/11/13 1001 item1 10 2003/11/14 2001 item1 10 2003/11/12 3001 item1 20 2003/11/14 1001 item1 20 2003/11/13 5when choosing the price for cust_no = '001', qty = 10 to 19 and effect_date >= '2003/11/14' , i have to return $2 and if for the cust_no = '001' , qty >= 20, effect_date >= '2003/11/13' i have to return $1The record may not be in order and the record number cannot be used to judge which is the last record entered. How to write this SQL statement??Thanks! |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2003-11-14 : 03:35:51
|
| What's the problem? Just select from the table with the criteria you need. Both of your examples only correspond to one row.-------Moo. :) |
 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-11-14 : 06:56:58
|
| You are going to need to explain the meaning of your 'qty' column. Seems to me that 10 >= 20 will resolve to false, given your sample data.Jay White{0} |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-11-14 : 09:50:50
|
| what is the primary key of this table? that is extremely important as well since if two rows satisfy your condition and both have the same effective date, how do we know which one is latest?- Jeff |
|
|
|
|
|
|
problem about selecting last record....