SQL EXCEPT TOP 10 records in Desc order

I want to pull all records except the TOP 10 in my statement but I keep running into problems when using 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 EXC开发者_StackOverflowEPT (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

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:

LIMIT 10..20 in SQL Server

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;

I can't reproduce your exact SQL configuration at this moment, but to answer the "general question":

• Select ALL data
• Select TOP 10 data
• Put EXCEPT in between the two SELECT queries

Example:

SELECT
    SUM(s.Records)
FROM
(
    -- List of ALL records
    SELECT
        dbo.people.name,
        dbo.people.address,
        Count (*) as Records
    FROM 
        dbo.people
    WHERE
        dbo.people.registration_date >= '01/01/2013'
        AND
        dbo.people.registration_date < '01/01/2014'
    GROUP BY
        dbo.people.name, dbo.people.address

    -- Exclusion
    EXCEPT

    -- TOP 10 list
    SELECT TOP 10
        dbo.people.name,
        dbo.people.address,
        Count (*) as Records
    FROM 
        dbo.people
    WHERE
        dbo.people.registration_date >= '01/01/2013'
        AND
        dbo.people.registration_date < '01/01/2014'
    GROUP BY
        dbo.people.name, dbo.people.address
    ORDER BY
        COUNT (*) DESC
) s

Assuming we have a database of people and their Name + Address combination qualifies them as a unique record (not on a relational level, but on a business requirements level).

EXCEPT has been available since SQL Server 2005.

You can use this as a simple query :

with CTP as (select top 10 id_site from sites order by id_site DESC)
select * from sites
where id_site not in (select id_site from CTP)