Hi tenchi,

You can't use "ORDER BY" in a sub query. That said, why do you need a sub query at all here?

I want to get the latest 10 record sorted in ascending order by time.

>You can't use "ORDER BY" in a sub query.

I tried to remove the ORDER BY from sub query, but the error still exist.

Also, when I remove the ORDER BY outside the sub query, 

the error becomes "Incorrect syntax near ')'. "

Hi tenchi,

I missed the "TOP" in your sub query, which indeed allows an "ORDER BY". What database are you using, Oracle or MSSQL? Anyway, the problem seems to stem from the fact the sub query needs a name, like this ("X" can be any valid name):

SELECT
 [userId],
 [groupId],
 [mainText],
 [tweetTime]
FROM
    (SELECT TOP @fetchRow
      {MST_USER}.[userId],
      {TBL_TALK}.[groupId],
      {TBL_TALK}.[mainText],
      {TBL_TALK}.[tweetTime]
     FROM
      {MST_USER},
      {TBL_TALK}
     WHERE
      {TBL_TALK}.[groupId] = @groupId
      AND {TBL_TALK}.[userId] = {MST_USER}.[userId]
     ORDER BY
      {TBL_TALK}.[tweetTime] DESC) AS X
ORDER BY
 {TBL_TALK}.[tweetTime] ASC

Oh great! i succeed by giving the sub query a name.

Thank you very much for the answer.

> What database are you using, Oracle or MSSQL?

I'm sorry I don't know because I'm using the free environment on the cloud.

Hi tenchi,

In that case (using the free environment) you are using an MSSQL database. MSSQL may have some different syntax then Oracle has, in case you're familiar with the latter (I was assuming that because of the way you join the two tables, without any explicit JOIN condition).

Oh yes, you are exactly right. I forgot to use JOIN

And I also forgot to remove "{TBL_TALK}" from the ORDER BY outside the subquery 

ORDER BY
 {TBL_TALK}.[tweetTime] ASC

Not using JOIN and instead specifying the join condition in the WHERE clause is something I've seen a lot from old-time Oracle developers. It works, but I personally prefer JOINS for clarity.