I'VE 3 QUESTIONS

23 Jul 2005 6:52 AM

Bpk. Adi Wira Kusuma

FIRST, If I make a view like this:

SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="D:\";Extended properties=DBase III')...dav

So If it is called from Client, Does Data to be read from client's D:\ or
server's D:\?
If Data is still read from server, how can data be read from client?

Second, At Ms Access, I ever make query like this.

SELECT noid, FIRST(Fddate) AS fdate
from TB1
GROUP BY noid

I wanna make like it in SQL Server 2000. Can I do it?

Third, I've data like it

field1                field2
----------------------------
a1                        3
a1                        4
a1                        23
b1                        35
b1                        30
b1                        31

I wanna delete records, but first record of group (field1) is not deleted.
How syntax SQL to do it?

23 Jul 2005 10:44 AM

Jens Süßmeyer

> So If it is called from Client, Does Data to be read from client's D:\ or
> server's D:\?

> If Data is still read from server, how can data be read from client?

It´s read from the server, if you want to read it from the client you have
to put the data on a network share that the server can reach and open it.

> SELECT noid, FIRST(Fddate) AS fdate
> from TB1
> GROUP BY noid

With no background information that´ll be just a guess to, but you can use
semething like MIN()

> How syntax SQL to do it?

Delete
From SomeTable ST
Where field2 NOT IN
(Select TOP 1 field2 From sometable Where ST2.field1 = ST.field1 order by
field2)

HTH, Jens Suessmeyer.

Show quote

"Bpk. Adi Wira Kusuma" <adi_wira_kus***@yahoo.com.sg> wrote in message
news:eH9MZy2jFHA.3448@TK2MSFTNGP12.phx.gbl...
> FIRST, If I make a view like this:
>
> SELECT *
> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="D:\";Extended properties=DBase III')...dav
>
> So If it is called from Client, Does Data to be read from client's D:\ or
> server's D:\?
> If Data is still read from server, how can data be read from client?
>
> Second, At Ms Access, I ever make query like this.
>
> SELECT noid, FIRST(Fddate) AS fdate
> from TB1
> GROUP BY noid
>
> I wanna make like it in SQL Server 2000. Can I do it?
>
> Third, I've data like it
>
> field1                field2
> ----------------------------
> a1                        3
> a1                        4
> a1                        23
> b1                        35
> b1                        30
> b1                        31
>
> I wanna delete records, but first record of group (field1) is not deleted.
> How syntax SQL to do it?
>
>

23 Jul 2005 11:44 AM

Alejandro Mesa

> FIRST, If I make a view like this:
>
> SELECT *
> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="D:\";Extended properties=DBase III')...dav
>
> So If it is called from Client, Does Data to be read from client's D:\ or
> server's D:\?
> If Data is still read from server, how can data be read from client?

It is read from the server. To read it from the client, try using the UNC
name of the shared folder in the "data source".

> Second, At Ms Access, I ever make query like this.
>
> SELECT noid, FIRST(Fddate) AS fdate
> from TB1
> GROUP BY noid
>
> I wanna make like it in SQL Server 2000. Can I do it?

Use MIN or MAX aggregate functions.

> Third, I've data like it
>
> field1                field2
> ----------------------------
> a1                        3
> a1                        4
> a1                        23
> b1                        35
> b1                        30
> b1                        31
>
> I wanna delete records, but first record of group (field1) is not deleted.
> How syntax SQL to do it?

delete t1
where exists(select * from t1 as a where a.field1 = t1.field1 and a.field2 <
t1.field2)

--or

delete t1
where field2 > (select min(a.field2) from t1 as a where a.field1 = t1.field1)

AMB

Show quote

"Bpk. Adi Wira Kusuma" wrote:

> FIRST, If I make a view like this:
>
> SELECT *
> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
>   'Data Source="D:\";Extended properties=DBase III')...dav
>
> So If it is called from Client, Does Data to be read from client's D:\ or
> server's D:\?
> If Data is still read from server, how can data be read from client?
>
> Second, At Ms Access, I ever make query like this.
>
> SELECT noid, FIRST(Fddate) AS fdate
> from TB1
> GROUP BY noid
>
> I wanna make like it in SQL Server 2000. Can I do it?
>
> Third, I've data like it
>
> field1                field2
> ----------------------------
> a1                        3
> a1                        4
> a1                        23
> b1                        35
> b1                        30
> b1                        31
>
> I wanna delete records, but first record of group (field1) is not deleted.
> How syntax SQL to do it?
>
>
>