SQL EXCEPT TOP 10 records in Desc order

Question

I'm want pull all records except the TOP 10 in my statement but I keep running into problems when doing my ORDER BY in my subquery "Incorrect Syntax near the keyword ORDER"

@ID INT
as
SELECT ComVID, VID, MID, Ucomment, UdateAdded, MemberId, UserName, Avatar

FROM table.miComments JOIN table.mbrProfile2 ON MID = MemberId 

WHERE VID = @ID EXCEPT (SELECT ComVID, VID, MID, Ucomment, UdateAdded, MemberId, UserName, Avatar FROM table.miComments JOIN table.mbrProfile2 ON MID = MemberId 
WHERE VID = @ID ORDER BY UdateAdded DESC) 'ERROR: Incorrect Syntax near the keyword ORDER'

ORDER BY UdateAdded DESC

Answer 1

You're talking about an offset. If you have a query returning rows 1,2,3,4,5,6,7 and you want to skip over the first 3 (yielding 4,5,6,7), you can specify an offset of 3.

In MySQL you can use the LIMIT clause which accepts an offset argument. In PostgreSQL you'll want the OFFSET clause. SQLServer (as of the last time I was forced to use it) doesn't support offsets.

PostgreSQL

OFFSET says to skip that many rows before beginning to return rows. OFFSET 0 is the same as omitting the OFFSET clause. If both OFFSET and LIMIT appear, then OFFSET rows are skipped before starting to count the LIMIT rows that are returned.

SELECT * FROM tbl OFFSET 10

MySQL

In MySQL you cannot specify an offset without also specifying a limit for some bizarre reason:

To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. This statement retrieves all rows from the 96th row to the last:

SELECT * FROM tbl LIMIT 95,18446744073709551615;

Answer 2

If you're using MS SQL Server, there is no LIMIT or OFFSET equivalent, so you'd have to use a subquery to accomplish what you want.

SELECT ComVID, VID, MID, Ucomment, UdateAdded, MemberId, UserName, Avatar 
FROM table.miComments 
JOIN table.mbrProfile2 ON MID = MemberId  
WHERE VID = @ID AND ComVID NOT IN (
   SELECT TOP 10 ComVID
   FROM table.miComments 
   JOIN table.mbrProfile2 ON MID = MemberId  
   WHERE VID = @ID ORDER BY UdateAdded DESC)  
ORDER BY UdateAdded DESC 

The following StackOverflow link has a lot more information in it, which may be helpful:

http://stackoverflow.com/questions/971964/limit-10-20-in-sqlserver