Here's my real situation.

Order "1-m" ListOrder
List "1-m" ListOrder

SELECT Order.* "
  FROM Order, ListOrder, List
  WHERE Order.OrderID=ListOrder.OrderID
    AND ListOrder.ListID=List.ListID
    AND List.ListManagerID = 12345

As you can see, it's a simple 3-table join where I'm trying to get all Orders placed for a particular ListManager. However, above query will return duplicate Orders which I don't need since there can be more than one ListOrder for each Order. In VFP, I could add "GROUP BY Order.OrderID" and I'll get back just one Order record for satisfied condition but it's illegal in MSSQL.

What can I do to get around this? It's seems so simple but... TIA.


>You do not.
>
>Why use a GROUP BY in the example you gave? There are no aggregated fields, so there's no reason to group by anything. If it's just to get a count of the distinct items in Field2, then the other fields are really extraneous, since the data will be meaningless anyway.
>
>For aggregated fields, it really makes sense to enforce this, if you think about it.
>
>e.g. if you have the following data
>

>Field1   Field2   Field3   Field4
>---------------------------------
>Hello    World    Bob      54
>Hello    World    Fred     89
>Howdy    Pard     John     47
>
>** VFP allowed
>SELECT field1, field2, field3, SUM(field4) AS field4 ;
>  FROM table ;
>  GROUP BY field1, field2 ;
>  INTO CURSOR cuSum
>
>** result set
>Field1   Field2   Field3   Field4
>---------------------------------
>Hello    World    ID1      143
>Bert     Ernie    ID3      47

>Since you wouldn't know that Field3 really had a different value for one of the records involved, Field3 contains meaningless information. If Field3 really is meaningless information, it should not be included in the field list; otherwise, it should be included in the GROUP BY as well.