CODEWITHSUNDEEP
sqlsql-servert-sql
Brandon Frenchak
Brandon Frenchak

Reputation: 496

Convert T-SQL query to ANSI SQL-99 standard

I'm studying for the SQL Server querying exam, and I got a demo of some practice questions from a website. One of the questions asks to pull from two tables (Customers and Orders), using the first initial as an alias, find the most recent order for each customer, and order it with the most recent orders first. Adventureworks has tables just like this (Sales.SalesOrderHeader and Sales.Customer), so I created this query and thought I had it solved.

SELECT 
    c.CustomerID, 
    MAX(o.OrderDate) OVER (PARTITION BY c.customerid) AS MostRecentOrderDate
FROM 
    Sales.SalesOrderHeader o 
INNER JOIN 
    Sales.Customer c ON o.CustomerID = c.CustomerID
ORDER BY 
    OrderDate DESC

But... at the end of the question it says to make sure it conforms to ANSI SQL-99 standard.

So I checked this code on

http://developer.mimer.com/validator/parser99/index.tml#parser

and it tells me

The following features outside Core SQL-99 are used:
T611, Elementary OLAP operations
F391, Long identifiers

Here's the definition of things included in T611 Elementary OLAP operations:

I'm not sure as to the exact reason why I'm getting the "Long Identifiers" error, but here's a link from Microsoft that has some stuff on it.

http://msdn.microsoft.com/en-us/library/hh544365(v=sql.105).aspx

Idk if this will actually help on the exam or not, but I was interested as to the answer.

Upvotes: 2

Views: 5968

Answers (5)

Sean Bradley
Sean Bradley

Reputation: 3577

The previously accepted answer to this question produces an error when tested on the adventureworks sample database

Column "SalesLT.SalesOrderHeader.OrderDate" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

There is a problem with the order by clause. You can fix it by using

select C.LastName, max(O.OrderDate) as MROD
from SalesLT.Customer C inner join SalesLT.SalesOrderHeader O
on C.CustomerID = O.CustomerID
group by C.LastName
order by max(O.OrderDate) Desc

or

select C.LastName, max(O.OrderDate) as MROD
from SalesLT.Customer C inner join SalesLT.SalesOrderHeader O
on C.CustomerID = O.CustomerID
group by C.LastName
order by MROD Desc

Both these examples pass the sql-99 validator test, but only when I use a short name, such as MROD for the max(O.OrderDate) alias, instead of MostRecentOrderDate.

and also, I just passed the exam in question.

Upvotes: 3

Nick.Mc
Nick.Mc

Reputation: 19194

By SQL-99 they might just mean user INNER JOIN and ON rather than joining in the WHERE clause.

Upvotes: 0

valex
valex

Reputation: 24144

Try this query:

select c.CustomerID, 
       MAX(o.OrderDate), 
from  SalesOrderHeader o
INNER JOIN Customer c ON o.CustomerID = c.CustomerID
group by o.CustomerID 
ORDER BY 2 DESC

Upvotes: 0

Nathan
Nathan

Reputation: 1425

Your ORDER BY is an elementary OLAP operation. You can safely remove it, your data just may not be displayed in the way you'd like.

The long identifier error is because you are referencing INFORMATION_SCHEMA.INFORMATION_SCHEMA_CATALOG_NAME. In other words, your Sales.SalesOrderHeader bit. You should be able to just reference the information schema catalog name, in your case SalesOrderHeader and Customer without specifying Sales..

Upvotes: 0

Ajith Sasidharan
Ajith Sasidharan

Reputation: 1155

Long identifier error is due to the fact that the identifier length is more that 30 characters. try this query it works ::

SELECT 
    c.CustomerID, 
    MAX(o.OrderDate) AS mrod
FROM 
    SalesOrderHeader o 
INNER JOIN 
    Customer c ON o.CustomerID = c.CustomerID
group by c.customerID
ORDER BY 
    o.OrderDate DESC

Upvotes: 3

Related Questions