Postgresql Join on Select Statement

Question

I want to do a join on a Select Statement in Postgresql, but I am having issues

SELECT 
    s.sessionid, sp.lang 
FROM 
    sessions s
INNER JOIN 
    pages sp 
ON 
    sp.sessionid =  s.sessionid 
INNER JOIN
(
    SELECT 
        max(sessionid)
    FROM 
        sessions
    AS 
        maxSession
)
ON 
    maxSession = s.sessionid
WHERE 
    --Where condition

I get the following error: ERROR: subquery in FROM must have an alias

LINE 6: (
        ^
HINT:  For example, FROM (SELECT ...) [AS] foo.

If I add the FROM

FROM
(
    SELECT max(sessionid)
    FROM sessions
)
AS maxSession

I get another error

ERROR:  syntax error at or near "FROM"
LINE 7:  FROM

Ideas?

remove the ALIAS on the subquery. it should be outside. ( SELECT max(sessionid) maxSession FROM sessions ) AS maxSession ON maxSession.maxSession = s.sessionid — John Woo
– John Woo, Commented Sep 23, 2013 at 21:49

Twelfth · Accepted Answer · 2013-09-23 21:51:58Z

22

You are close.

  INNER JOIN
 (
  SELECT 
    max(sessionid) as 'maxSession'
   FROM 
   sessions        
) maxSession
ON 
maxSession.maxSession = s.sessionid

Any time you refer to a query as a table, you need to give it an alias...alias goes right after the entire subquery, not in the subquery itself.

answered Sep 23, 2013 at 21:51

Twelfth

7,2183 gold badges30 silver badges35 bronze badges

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

2 Comments

John Woo Over a year ago

one more missing, you need to give an alias for max(sessionid)

Twelfth Over a year ago

ah yes, sloppy on my part. fixxed...and just noticed you got to the error in a comment before my answer was posted.

Collectives™ on Stack Overflow

Postgresql Join on Select Statement

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related