sql server syntax error near where in subquery

Question

I am trying to get the last status a customer had. By browsing examples, I have put together this query, which works:

SELECT table.Card, 
    table.Type, 
    table.Date, 
    ROW_NUMBER() OVER   
        (PARTITION by table.CARD ORDER BY table.date DESC, table.TYPE, table.DATE) rn 
 FROM table 
 WHERE table.Type = 'active' or table.Type = 'erased'

From there, I just need those records where rn=1, but as soon as I try to use itas a subquery as shown below, I get a syntaxis error:

SELECT * FROM (
    SELECT table.Card, 
        table.Type, 
        table.Date, 
        ROW_NUMBER() OVER   
            (PARTITION by table.CARD ORDER BY table.date DESC, table.TYPE, table.DATE) rn 
     FROM table 
     WHERE table.Type = 'active' or table.Type = 'erased'
) WHERE rn=1

It must something simple I am just missing, but I am totally confused. Any help will be appreciated.

Your subquery needs an alias. ) AS x WHERE rn = 1;

Aaron Bertrand
– Aaron Bertrand

2014-10-02 19:10:12 +00:00
Commented Oct 2, 2014 at 19:10 — Aaron Bertrand
– Aaron Bertrand, Commented Oct 2, 2014 at 19:10

Kamil Gosciminski · Accepted Answer · 2014-10-02 19:09:45Z

2

You should assign an alias to the INNER select. Try this.

SELECT * FROM (
    SELECT table.Card, 
        table.Type, 
        table.Date, 
        ROW_NUMBER() OVER   
            (PARTITION by table.CARD ORDER BY table.date DESC, table.TYPE, table.DATE) rn 
     FROM table 
     WHERE table.Type = 'active' or table.Type = 'erased'
) foo WHERE rn=1

answered Oct 2, 2014 at 19:09

Kamil Gosciminski

17.3k9 gold badges53 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kamil Gosciminski Over a year ago

I'm glad. Please accept the answer if it works for you. Remember, if you use alias on your sub-query then you can JOIN it with some other tables using that alias :)

Collectives™ on Stack Overflow

sql server syntax error near where in subquery

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related