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 |
|
rookie_sql
Constraint Violating Yak Guru
443 Posts |
 Posted - 2007-10-15 : 06:14:42
|
| I have data where i want to use a group by. But i have columns where i can't group by they are the serial numbers and total price as they are different values in each field all other data has the same data so i can use the group by how do i get around the others ? |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2007-10-15 : 06:26:52
|
| If you don't want to group by serial numbers, you'll have to leave them out of your query. As for the price, could you use SUM(price)?Jim |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-10-15 : 07:19:09
|
| Can you post some sample data with expected result?MadhivananFailing to plan is Planning to fail |
|
|
|
rookie_sql
Constraint Violating Yak Guru
443 Posts |
Posted - 2007-10-15 : 08:26:44
|
| Here is my test data, VOUCHER_ID CONTRACT_ID [Date] TOTAL_PRICE SERIAL_ID12016010 840376 2007-10-09 69 383019565212016010 840376 2007-10-09 56 3830141153i like to Group by on all the columns and get a max date |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-10-15 : 08:58:16
|
SELECT t.Voucher_ID, t.Contract_ID, t.[Date], t.Total_Price, t.Serial_IDFROM Table1 AS tWHERE t.Serial_ID = (SELECT TOP 1 r.Serial_ID FROM Table1 AS r WHERE r.Voucher_ID = t.Voucher_ID AND r.Contract_ID = t.Contract_ID ORDER BY r.[Date] DESC, r.Total_Price DESC, r.Serial_ID DESC)
E 12°55'05.25"N 56°04'39.16" |
|
|
|
|
|
|
Group by data