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 |
|
BrandonB
Starting Member
2 Posts |
 Posted - 2014-01-14 : 16:10:51
|
| I've run into a problem several times where I think I am using distinct and/or group by properly, but the results still have duplicates. Any help is appreciate, thanks! Code is as follows (temp table creation not shown)select distinct ts.StockNumber ,ts.TradeStocknumber ,ts.SalesDate ,sb.SalesBalance ,tb.TradeBalanceFrom #trades ts left join #Salesbalance sb on sb.StockNumber = ts.StockNumber left join #tradebalance tb on tb.TradeStocknumber = ts.TradeStocknumberwhere TradeBalance is not null or SalesBalance is not null group by ts.StockNumber, ts.TradeStocknumber, sb.SalesBalance,tb.TradeBalance,ts.SalesDate |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-01-14 : 17:30:26
|
| You should get only one row per unique combination of all 5 columns. If any one of the columns has a different value, then uniqueness is lost.If that is not what you are seeing - i.e., if you are seeing two rows that are identical in all columns, can you post the sample data in a consumable format? |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-01-15 : 05:00:03
|
quote:
Originally posted by BrandonB
I've run into a problem several times where I think I am using distinct and/or group by properly, but the results still have duplicates. Any help is appreciate, thanks! Code is as follows (temp table creation not shown)select distinct ts.StockNumber ,ts.TradeStocknumber ,ts.SalesDate ,sb.SalesBalance ,tb.TradeBalanceFrom #trades ts left join #Salesbalance sb on sb.StockNumber = ts.StockNumber left join #tradebalance tb on tb.TradeStocknumber = ts.TradeStocknumberwhere TradeBalance is not null or SalesBalance is not null group by ts.StockNumber, ts.TradeStocknumber, sb.SalesBalance,tb.TradeBalance,ts.SalesDate
first show as some sample data and explain us what according to you are duplicates and how you want data to appear in your output.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
BrandonB
Starting Member
2 Posts |
Posted - 2014-01-15 : 13:05:59
|
| Thank you both. James led me to the answer but I realized that including the Sales Date in the temp table threw me off, creating unique rows for each date when I only wanted unique rows for each stock number. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-01-16 : 04:50:23
|
quote:
Originally posted by BrandonB
Thank you both. James led me to the answer but I realized that including the Sales Date in the temp table threw me off, creating unique rows for each date when I only wanted unique rows for each stock number.
ok good------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|
|
Group By/Distinc Not working