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 |
GuyZommer
Starting Member
2 Posts |
Posted - 2013-01-14 : 01:11:15
|
Hello,I have sales transaction table that have the follwing fields:BDCUST (Cust number)BDCAT (Item number)BDQTY (Quantity)BDVUC (Net price)BDTOPN(Invoice date)What I need is to present the last transaction for each customer + itemI tried the following but I get duplicationSELECT BD.BDCUST, BD.BDCAT, BD.BDQTY, BD.BDVUC, Max(BD.BDTOPN)FROM GALF2.BD BDWHERE (BD.BDTOPN Between 20120101 And 20130113) AND (BD.BDCAT>='2300000')GROUP BY BD.BDCUST, BD.BDCAT, BD.BDQTY, BD.BDVUCORDER BY BD.BDCAT, Max(BD.BDTOPN) DESC |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-14 : 02:52:57
|
Use the row_number function (assuming you are using SQL 2005 or later)SELECT BD.BDCUST, BD.BDCAT, BD.BDQTY, BD.BDVUC, BD.BDTOPNFROM( SELECT BD.BDCUST, BD.BDCAT, BD.BDQTY, BD.BDVUC, BD.BDTOPN, ROW_NUMBER() OVER (PARTITION BY BDCUST,BDCAT ORDER BY BDTOPN DESC) AS NN FROM GALF2.BD BD WHERE (BD.BDTOPN BETWEEN 20120101 AND 20130113) AND (BD.BDCAT >= '2300000')) rWHERE NN = 1 ORDER BY BDCAT, BD.BDTOPN DESC |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-14 : 03:18:53
|
by last transaction for each customer + itemdo you mean last transaction of customer and its associated item?or last transaction of customer for an item?if latter you can use given suggestion aboveif former it should beSELECT BD.BDCUST, BD.BDCAT, BD.BDQTY, BD.BDVUC, BD.BDTOPNFROM( SELECT BD.BDCUST, BD.BDCAT, BD.BDQTY, BD.BDVUC, BD.BDTOPN, ROW_NUMBER() OVER (PARTITION BY BDCUST,BDCAT ORDER BY BDTOPN DESC) AS NN FROM GALF2.BD BD WHERE (BD.BDTOPN BETWEEN 20120101 AND 20130113) AND (BD.BDCAT >= '2300000')) rWHERE NN = 1 ORDER BY BDCAT, BD.BDTOPN DESC ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
GuyZommer
Starting Member
2 Posts |
Posted - 2013-01-14 : 03:53:12
|
Hi,Thanks for the prompt reply. I am using Microsoft Query.I tried the code you suggest and I get the following error:"Could not add the table '('. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-14 : 04:05:27
|
quote: Originally posted by GuyZommer Hi,Thanks for the prompt reply. I am using Microsoft Query.I tried the code you suggest and I get the following error:"Could not add the table '('.
You might be better off posting it in some Excel forums then. This is MS SQL Server forum and we deal with T-sql. The posted code works fine in t-sql.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|