Posted by Erland Sommarskog on 11/27/07 22:57

(bwalton_707@yahoo.com) writes:
> A simple select statement where I want to return the most current date
> from a table along with the unique identifier for the row selected is
> a single select statement in VFP
>
> SELECT TOP 1 date, id
> FROM anytable
> ORDER BY id, date desc
> GROUP BY id
>
> OR Another example
>
> SELECT invoice.number, customer.name, customer.address, invoice.id,
> customer.id
> FROM invoice
> INNER JOIN customer
> ON invoice.customerid = customer.id
> ORDER BY customer.id, invoice.date DESC
> GROUP BY customer.id
>
> This will return the most recent order for a customer
>
> Niether selects statements are supported in SQL Server 2005... Is
> there a logical reason WHY? Other then ansi standards which I'm not
> buying as m$ft rarely follows any standards but there own 100% of the
> time anyway.

Any logical reason? Well, in 4.x of SQL Server you were permitted to
have columns in the SELECT list that was not in the GROUP BY clause,
and I hated the feature. Everytime I did that error, I got a long output
of complete garbage, instead of a useful error message.

Simply, GROUP BY stands for aggregation, so if you group by A, B and
C and say that you also want D, the list - what does that mean? It can
make sense if D is dependent on one of A, B or C. For instance this
applies to customer.name in your example. But what is going to
happen if there are multiple values of invoice.id for the same
customer.id? I'm afraid that it plainly doesn't make any sense, whatever
you prefer to read into it.

To get the most recent invoice for each customer, this is the best
way to do it in my opinion:

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

It's nice for several reasons:
1) It's fully ANSI-compatible.
2) It's easy to extend to "show the three latest invoices".
3) It is likely to be very effecient.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]