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 |
|
mdhingra01
Posting Yak Master
179 Posts |
 Posted - 2004-03-09 : 10:38:41
|
| The following statement retrieves 28 records in Foxpro beacuse of the group by function. However in SQL, I can not do a group by because I am not performing an aggregate. Therefor I retrieve 850 records. Column A1 is unique in all rows of the 850, which makes me question how the query in Foxpro can return only 28 records with the group by. If I were to remove A.1 from the SQl query and add a distinct to my statement, I retrieve the 28 records that foxpro gives me. Weird?....FoxPro Code:Select A.1, left(A.1,1) + A.4, A.2;from A;where left(A.1,1) + A.4 not in (select alltrim(B.3) from B);group by (left(A.1,1) + A.4)SQL Code:Select A.1, left(A.1,1) + A.4, A.2from Awhere left(A.1,1) + A.4 not in (select alltrim(B.3) from B) |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-03-09 : 11:27:33
|
| A group by clause outputs rows that are unique with respect to the group by clause.So Foxpro will be giving unique (left(A.1,1) + A.4) hence the 28 rows.The question is what it does with A.1 and A.2 - it could just be taking random values from the group (first? whatever that means) or maybe it has an implied max or min.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
mdhingra01
Posting Yak Master
179 Posts |
Posted - 2004-03-09 : 11:29:06
|
| Please disregard.... FYIIn foxpro, a group by will automatically do a max on the A.1 column, therefore returning only 28 records. In order to mimic this in SQL add a max to A.1 and group by at the end of the statement. |
|
|
|
|
|
|
|
|
Foxpro vs SQL Server