|
|
Posted by bwalton_707 on 11/28/07 00:04
On Nov 27, 6:43 pm, bwalton_...@yahoo.com wrote:
> On Nov 27, 6:27 pm, bwalton_...@yahoo.com wrote:
>
>
>
>
>
> > On Nov 27, 5:34 pm, "Tony Rogerson" <tonyroger...@torver.net> wrote:
>
> > > Both are very different products.
>
> > > Try this...
>
> > > SELECT id, max( date )
> > > FROM anytable
> > > GROUP BY id
> > > ORDER BY id, date desc
>
> > > The jibe about ansi standards and MS not following them is a bit cheap, in
> > > SQL Server you can use the ansi standard if you want - well, a lot of the 92
> > > implementation anyway.
>
> > > I'm not familiar with vfp - does it follow the ansi standard - the syntax
> > > you posted doesn't look familiar.
>
> > > --
> > > Tony Rogerson, SQL Server MVPhttp://sqlblogcasts.com/blogs/tonyrogerson
> > > [Ramblings from the field from a SQL consultant]http://sqlserverfaq.com
> > > [UK SQL User Community]
>
> > Hi Tony,
>
> > VFP supports multiple standards including their own sql standards.
> > There is an engine behavior flag that governs which variation FoxPro
> > Uses.
>
> > If you are interested here is a link to another forum about it...http://fox.wikis.com/wc.dll?Wiki~Enginebehavior~VFP
>
> > The syntax I ended up using was a variant of this, with part of it
> > wrapped in a CTE for readability.
> > Just seemed like a lot of code for such a simple task ... Moveover my
> > last post was incorrect it should
> > not have said duplicate records.
>
> > SELECT I1.invoice_nbr, C.customer_name, C.shopping_addr, C.acct_nbr
> > FROM Invoices AS I1,
> > Customers AS C
> > WHERE I1.acct_nbr = C.acct_nbr
> > AND I1.posting_date
> > = (SELECT MAX(I2.posting_date)
> > FROM Invoices AS I2
> > WHERE I2.acct_nbr = C.acct_nbr);
>
> > I only been coding in sql server 2005 for about 6 month... I been
> > using foxbase then vfp forever however with it's end of life all new
> > projects are in sql
> > and there just seems to be ALOT more coding involved to accomplish the
> > same task, especially when doing anything on a row by row level ....
> > SQL also lacks a built in debugger going into .NET is a pain . . .
>
> > But what can you do :) ....
>
> > I will try your suggestion and thanks everyone for the help...
>
> > Bryan- Hide quoted text -
>
> > - Show quoted text -
>
> Erland,
>
> Thanks for the post
>
> That is exactly the feature, I was referring to, thanks for
> clarifing ...
>
> Bryan- Hide quoted text -
>
> - Show quoted text -
SELECT i.number, c.name, c.address, i.id, c.address
FROM customer c
JOIN (SELECT number, id, customerid
rowno = row_number() OVER(PARTITION BY customerid
ORDER BY date DESC)
FROM invoices) AS i ON i.customerid = c.id
WHERE i.rowno = 1
ORDER BY c.i
This is what I ended up using ... Worked Perfectly and performs better
then what I had ...
Thanks Much
Bryan
[Back to original message]
|