Re: ORDER BY AND GROUP BY CLAUSE

On Nov 27, 6:43 pm, bwalton_...@xxxxxxxxx wrote:
On Nov 27, 6:27 pm, bwalton_...@xxxxxxxxx wrote:





On Nov 27, 5:34 pm, "Tony Rogerson" <tonyroger...@xxxxxxxxxx> 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
.

Relevant Pages

  • Re: Where to do a SQL Lookup: map or orchestration
    ... Also be wary of concatenating columns in views as there is more work for SQL to do on building the results. ... I've written a simple custom functoid that stores the RecordSet from a lookup in a hashtable. ... customer part number and the CustomerId as criteria. ...
    (microsoft.public.biztalk.general)
  • Re: dynamische WHERE Klausel
    ... Das so zu machen ist aber alles andere als eine gute Idee: Bei SQL kommt es nicht darauf an, den Code möglichst trickreich zu schreiben, ... declare @CustomerID int ... --WHERE CustomerID BETWEEN 0 AND 2147483647 ...
    (microsoft.public.de.sqlserver)
  • Where to do a SQL Lookup: map or orchestration
    ... I have a question about where to handle a SQL Adapter lookup. ... I have purchase orders messages coming from Customers. ... customer part number and the CustomerId as criteria. ...
    (microsoft.public.biztalk.general)
  • Re: Primary Key / Filtering problem thinking hard.
    ... Assuming that CustomerId is a text field, then to match all customers with a ... wild card, you need to use: ... the filter command is a VALID SQL "where" clause without the word ... Dim strSearchFor As String ...
    (microsoft.public.access.forms)
  • Re: Delete Query does not work!
    ... Here is the SQL: ... FROM StudentRecord INNER JOIN DischargeLog ON StudentRecord.RegID = ... >>possible causes of failure such as ownership or read only ... >>and the other table contains the customerID as well as the ...
    (microsoft.public.access.queries)