last record per id view

I have an Oracle table with assets, periods and various other fields.
Can a create a view to show me the records for the last period per asset?

The 2 stage SQL all processed at the client end would be something like
SELECT asset, MAX(period) AS maxper ;
FROM table ;
GROUP BY asset ;
INTO CURSOR foo

SELECT table.* ;
FROM table, foo ;
WHERE table.asset=foo.asset ;
AND table.period=foo.maxper

but I don't want to drag a gazillion records into Fox to do that if I can
avoid it by making the server process it before sending.


--
TIA
Andrew Howell

Cindy
Thu Mar 30 09:52:02 CST 2006

Hi Andrew,

Try this:

Select Asset, Period ;
From Table ;
Where Period = ;
(Select Max(T2.Period) From Table T2 Where T2.Asset = Table.Asset)

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@msn.com www.cindywinegarden.com


"Andrew Howell" <ajh@work> wrote in message
news:eTHdsqAVGHA.4960@TK2MSFTNGP12.phx.gbl...
>I have an Oracle table with assets, periods and various other fields.
> Can a create a view to show me the records for the last period per asset?
>
> The 2 stage SQL all processed at the client end would be something like
> SELECT asset, MAX(period) AS maxper ;
> FROM table ;
> GROUP BY asset ;
> INTO CURSOR foo
>
> SELECT table.* ;
> FROM table, foo ;
> WHERE table.asset=foo.asset ;
> AND table.period=foo.maxper
>
> but I don't want to drag a gazillion records into Fox to do that if I can
> avoid it by making the server process it before sending.
>
>
> --
> TIA
> Andrew Howell
>

MarkLovely
Thu Mar 30 14:17:02 CST 2006

Andrew,

If you use SqlExec() only the results will be returned by Oracle.

Mark

"Andrew Howell" wrote:

> I have an Oracle table with assets, periods and various other fields.
> Can a create a view to show me the records for the last period per asset?
>
> The 2 stage SQL all processed at the client end would be something like
> SELECT asset, MAX(period) AS maxper ;
> FROM table ;
> GROUP BY asset ;
> INTO CURSOR foo
>
> SELECT table.* ;
> FROM table, foo ;
> WHERE table.asset=foo.asset ;
> AND table.period=foo.maxper
>
> but I don't want to drag a gazillion records into Fox to do that if I can
> avoid it by making the server process it before sending.
>
>
> --
> TIA
> Andrew Howell
>
>
>

Andrew
Fri Mar 31 01:48:04 CST 2006

"Cindy Winegarden" <cindy_winegarden@msn.com> wrote in message
news:O0bcgIBVGHA.5852@TK2MSFTNGP10.phx.gbl...
> Hi Andrew,
>
> Try this:
>
> Select Asset, Period ;
> From Table ;
> Where Period = ;
> (Select Max(T2.Period) From Table T2 Where T2.Asset = Table.Asset)

That selects no records.
Initially I had

Select * ;
From Table ;
Where Period = ;
(Select Max(T2.Period) From Table T2 Where T2.Asset = Table.Asset)

since I need all the fields, not just the asset and period but that started
selecting everything from the table before I cancelled it.

--
regards
Andrew Howell

Cindy
Fri Mar 31 15:26:27 CST 2006

Hi Andrew,

When I run a similar query in FoxPro I get only the appropriate Max() rows.
Have you considered using SQL Pass-through rather than creating a remote
view? When you try SQL Pass-through is it faster or do you still get too
many rows from the server?

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@msn.com www.cindywinegarden.com


"Andrew Howell" <ajh@work> wrote in message
news:OFxiHnJVGHA.4300@TK2MSFTNGP14.phx.gbl...
> "Cindy Winegarden" <cindy_winegarden@msn.com> wrote in message
> news:O0bcgIBVGHA.5852@TK2MSFTNGP10.phx.gbl...

> That selects no records.
> Initially I had
>
> Select * ;
> From Table ;
> Where Period = ;
> (Select Max(T2.Period) From Table T2 Where T2.Asset = Table.Asset)
>
> since I need all the fields, not just the asset and period but that
> started selecting everything from the table before I cancelled it.

Andrew
Mon Apr 03 02:41:21 CDT 2006

"Cindy Winegarden" <cindy_winegarden@msn.com> wrote in message
news:Ohn3snQVGHA.484@TK2MSFTNGP10.phx.gbl...
> Hi Andrew,
>
> When I run a similar query in FoxPro I get only the appropriate Max()
> rows. Have you considered using SQL Pass-through rather than creating a
> remote view? When you try SQL Pass-through is it faster or do you still
> get too many rows from the server?

Hi Cindy,
thanks for the answers. I just tried with SPT but it was running for
over 10 minutes. I killed Fox and now I any SPT I try to execute just
returns a -1 for connection level error.

I've just pulled the whole table and done it in Fox.

--
regards
Andrew Howell