时间戳

首页 > 解决方案 > Select with Row_Number using join. Fetching last three customers with their respective addresses

sql - Select with Row_Number using join. Fetching last three customers with their respective addresses

问题描述

I want to fetch the last three customers from my database with their respective addresses without using TOP statement because using that way should be wrong. For that purpose I'm using the ROW_NUMBER() function.

This is my query:

SELECT *
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY c.customerId DESC) AS lastCustomers, *
    FROM Customer c JOIN Address a ON c.customerId = a.customerId
) 
WHERE lastCustomers <= 3

When I'm executing this query I've got the following error:

Incorrect syntax near the keyword 'WHERE'.

Can anyone solve this syntax error for this query?

标签： sqlsql-server

解决方案

SQL Server -- as with many other databases -- requires table aliases for subqueries:

SELECT ca.*
FROM (SELECT ROW_NUMBER() OVER (ORDER BY c.customerId DESC) AS lastCustomers,
             c.*, a.*   -- might get an error for repeated columns
      FROM Customer c JOIN
           Address a
           ON c.customerId = a.customerId

      ) ca
WHERE lastCustomers <= 3;

This will then generate an error because the subquery has repeated columns.

Generally, this is easier to write as:

SELECT TOP (3) ROW_NUMBER() OVER (ORDER BY c.customerId DESC) AS lastCustomers,
             c.*, a.*   -- might get an error for repeated columns
FROM Customer c JOIN
     Address a
     ON c.customerId = a.customerId
ORDER BY c.customerId DESC;

推荐阅读