SQL: How to Join 2 SELECT statements

Question

I am trying to join these 2 select statements in SQL Server, but I'm getting an error.

SELECT 
    t1.AutoNumber, t1.last_autonumber, t1.ContactID, t2.KeyValue_String
FROM
    (SELECT 
         la.autonumber, 
         LAG(la.autonumber, 1) OVER (PARTITION BY la.ContactID ORDER BY la.issuedate asc) AS last_autonumber,
         la.ContactID 
     FROM 
         loanagreements la
     ORDER BY 
         la.ContactID desc, la.issuedate asc) as t1
LEFT JOIN
    (SELECT 
         la.autonumber, es.KeyValue_String 
     FROM 
         loanagreements la
     JOIN 
         Enum.LoanStatuses es ON la.LoanStatus = es.KeyValue_Int) as t2 ON t1.last_autonumber = t2.autonumber

Error messages:

Msg 1033, Level 15, State 1, Line 62
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

Msg 102, Level 15, State 1, Line 65
Incorrect syntax near 't2'.

So... what kind of error? might be useful to share it with us. — SqlZim, Feb 17 '17 at 21:11

Ismail Adar · Accepted Answer · 2017-02-17T21:16:08.413

3

you cannot use order by in subquery. You can edit your query. Try this.

select 
    t1.AutoNumber
  , t1.last_autonumber
  , t1.ContactID
  , t2.KeyValue_String
from (
    select 
        la.autonumber
      , last_autonumber = lag(la.autonumber, 1) over (
        partition by la.ContactID 
            order by la.issuedate asc
        )
    , la.ContactID
   from loanagreements la
   --order by la.ContactID desc, la.issuedate asc
   ) as t1
  left join (
   select 
      la.autonumber
    , es.KeyValue_String
    from loanagreements la
    inner join Enum.LoanStatuses es 
      on la.LoanStatus = es.KeyValue_Int
   ) as t2 
    on t1.last_autonumber = t2.autonumber

edited Feb 17 '17 at 21:16

answered Feb 17 '17 at 21:12

Ismail Adar

96
2

No reason why you can't use ORDER BY in a subquery... though I agree it doesn't contribute anything. Without seeing the OP's error message, though, we're only guessing what the problem is. – Ray O'Donnell Feb 17 '17 at 21:16
Thanks! Works like a charm. – Riley Hun Feb 17 '17 at 21:23
@Ray O'Donnell You can't use Order By in a sub query unless you also use include a top statement (however top 100 percent is valid.) "The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified." – Greg Feb 17 '17 at 21:25

SQL: How to Join 2 SELECT statements

1 Answers1