CODEWITHSUNDEEP
sqlsql-server
Axy
Axy

Reputation: 384

Beginner SQL query with ROW_NUMBER

i'm kind of a beginner with SQL.

Right now i'm trying to create a bit complex select but i'm getting some error, which I know it's a beginner mistake.

Any help appreciated.

SELECT ROW_NUMBER() OVER (ORDER BY score) AS rank, userID, facebookID, name, score FROM (
    SELECT * FROM Friends  AS FR WHERE userID = ? 
    JOIN 
    Users WHERE Users.facebookID = FR.facebookFriendID
) 
UNION (
    SELECT * FROM User WHERE userID = ?
)

Where the 2 ? will be replaced with my user's ID.

The table User contains every user in my db, while the Friends table contains all facebookFriends for a user.

USER TABLE
userID | facebookID | name | score

FRIENDS TABLE 
userID | facebookFriendID

Sample data

USER 

A | facebookID1 | Alex | 100
B | facebookID2 | Mike | 200

FRIENDS

A | facebookID2 
A | facebookID3
B | facebookID1

I'd like this result since Alex and mike are friends:

rank | userID | facebookID  | name
1    | B      | facebookID2 | Mike
2    | A      | facebookID1 | Alex

I hope this was quite clear explanation.

I'm getting this error at the moment:

Error occurred executing query: Incorrect syntax near the keyword 'AS'.

Upvotes: 0

Views: 188

Answers (1)

sgeddes
sgeddes

Reputation: 62831

You've got several issues with your query. JOINS come before WHERE clauses. And when using a JOIN, you need to specify your ON clauses. Also when using a UNION, you need to make sure the same number of fields are returned in both queries.

Give this a try:

SELECT ROW_NUMBER() OVER (ORDER BY score) AS rank, userID, facebookID, name, score 
FROM ( 
    SELECT * 
    FROM Users
    WHERE UserId = 'A'
    UNION 
    SELECT U.userId, u.facebookId, u.name, u.score 
    FROM Friends FR 
        JOIN Users U ON U.facebookID = FR.facebookFriendID 
    WHERE FR.userID = 'A' ) t

SQL Fiddle Demo

Also, by the way your using ROW_NUMBER, it really will be a Row Number vs a RANK. If you want Rankings (with potential ties), replace ROW_NUMBER with RANK.

Upvotes: 1

Related Questions