UKC

A slight variation here, in that the code works but I don't understand why. Can anyone tell me what the "any_string_here" does after the brackets where the table is partitioned. It works with any characters but I don't understand why.

Thanks in adavance

Dave

-------------------------------------------------------

DECLARE @TABLE TABLE (A INT, B INT,C VARCHAR(6))

INSERT INTO @TABLE VALUES
(1,123,'abc'),(1,122,'abc'),
(1,121,'abc'),(2,23,'z'),
(2,23,'efg'),(2,23,'ahij')

SELECT * FROM @TABLE

SELECT
A,B,C,ROWNUM FROM(
SELECT A,B,C,ROW_NUMBER() OVER(PARTITION BY A ORDER BY B DESC) ROWNUM
FROM @TABLE
) any_string_here
WHERE ROWNUM = 1

In reply to mostly harmless:

I reckon it's label so you can call it what you like, hence the 'any_string_here'. It does seem odd that it would be required though, but then it looks like Oracle. Try it without the label, it'll probably still work.

In reply to Davvers: Forgot to mention it's MSSQL 2008 and it doesn't work without the string.

In reply to mostly harmless: The post above is correct any_string_here is the identifier for the subquery above it. In SQL Server syntax, subqueries must have identifiers even if you then do not use them.

YOu can test this by trying this query
--------
SELECT A,B,C FROM
(SELECT A, B, C FROM TABLE)
any_string_here
------------
then this one
-------------
SELECT any_string_here.A,B,C FROM
(SELECT A, B, C FROM TABLE)
any_string_here
------------
then this one
------------
SELECT another_string_here.A,B,C FROM
(SELECT A, B, C FROM TABLE)
any_string_here
-----------


The first two will work, the last will fail because the 'multipart identifier another_string_here.A could not be bound' or some such error.

In reply to mostly harmless: It's an alias for the sub-query. The full syntax would be ) AS <any text here> You need to alias a sub-query as per SQL syntax, it's not of any use in this example but if you wanted to join the results of the sub-query to another table it give you a name to reference it as.

In reply to highaltitudebarista: Thanks everyone, I think I get it now.