>Hi All
>
>I can easily do this in VFP
>
>

SELECT <pkfield>,max(field2) as maxfield from <table1> group by <pkfield> into cursor <cursor1>

>
>and then...
>
>

SELECT <field1> from <table1> where <pkfield> in (select <pkfield> from <cursor1>) into cursor <cursor2>

>
>Question is, how can I do this with SQL Server? I'm assuming I won't be able to create a stored procedure on the client's database.
>
>Thanks in advance!

Which data access approach are you using? If it's ADO, you could send the two commands together in an ADO command execute or an ADO recordset Open, and use a temporary table.

So, something like this (I am going with your pseudo-code examples, and hopefully I am not mangling any of the logic you intended):

strSQL = "SELECT pkfield,max(field2) as maxfield " + ;
         "into #Tmp " + ;
         "from table1 group by pkfield; " + ;
         "SELECT field1 " + ;
         "from table1 " + ;
         "where pkfield in (select pkfield from #Tmp) "

Temporary tables have their drawbacks, but if you will need to access the first cursor/temp table for several subsequent selects, and you are constrained in what you can dictate regarding database access, sprocs, etc., they can help.

Drawbacks include...
1) the temporary table will not have any indexes, so the subsequent select against it could be less-than-optimized.
2) the SQL Server SELECT INTO syntax is not standard with other backends. This may or may not be an issue.
3) the optimizer deals with a statement at a time, without knowing how it will be used by the next statement ...

Derived tables might help, since they overcome points 1 & 3 (but, are not standard, so point 2 still applies). The derived table approach might look like this:

strSQL = "SELECT field1 " + ;
         "from " + ;
         "   (SELECT pkfield,max(field2) as maxfield " + ;
         "   from table1 group by pkfield ) DTab ; " + ;

Hope this helps. The SQL Server forum would be a good spot to post, as well.