SQL Query Except then select TOP 10

Question

I have the following query where I EXCEPT two results in Microsoft SQL Server

SELECT *
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (1)

EXCEPT

SELECT *
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (2)

ORDER BY News.PublishDate DESC

The result has lots of records, but I only want the first 10 results. How can I accomplish this?

Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using `postgresql`, `oracle`, `sql-server`, `db2`, ... — , Nov 23 '18 at 09:01
Since you select all columns, the EXCEPT wont remove anything. NewsAudience.PortalId = 1 for first select, and 2 for second select. — jarlh, Nov 23 '18 at 09:54

score 1 · Answer 1 · answered Nov 23 '18 at 12:33

Your query doesn't make sense. You are getting all rows from the first subquery. Why? Because you are using select * and NewsAudience.PortalId is in the select list. That value is automatically different based on the conditions in the subqueries.

I would recommend phrasing your need differently. For instance, you might intend:

SELECT n.*
FROM News n
WHERE EXISTS (SELECT 1
              FROM NewsAssignment na JOIN
                   NewsAudience nau
                   ON na.NewsAudienceId = nau.Id AND na.PortalId IN (1)
              WHERE n.Id = na.NewsId
             ) AND
      NOT EXISTS (SELECT 1
                  FROM NewsAssignment na JOIN
                       NewsAudience nau
                       ON na.NewsAudienceId = nau.Id AND na.PortalId IN (2)
                  WHERE n.Id = na.NewsId
                 )
ORDER BY n.PublishDate DESC;

You can add TOP (10) to the outer query if duplicates are still a problem after you have correct the logic.

score 0 · Answer 2 · answered Nov 23 '18 at 09:00

0

Use LIMIT:

Select *
FROM
(
SELECT *
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (1)
ORDER BY News.PublishDate DESC

EXCEPT

SELECT *
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (2)
ORDER BY News.PublishDate DESC
)a
LIMIT 10;

answered Nov 23 '18 at 09:00

Mayank Porwal

33,470
8
37
58

I tried something like that but I get an error at "a" The column "CustomerId" was specified multiple times for "a" – FerronSW Nov 23 '18 at 09:02
But the inner query runs fine? – Mayank Porwal Nov 23 '18 at 09:03
@FerronSW - you need to remove `*` and list columns explicitly (good habit to be in anyway). SQL can return *result sets* to a client that contain multiple columns with the same name but balks at allowing such things in subqueries etc. (You at least need to do the two that form the `except`, whether you also do the outer one is a matter of taste) – Damien_The_Unbeliever Nov 23 '18 at 09:05

score 0 · Answer 3 · answered Nov 23 '18 at 09:14

use top keyword as you are using sql server

select top 10 t.* from 
(
SELECT *
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (1)

EXCEPT

SELECT *
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (2)
) t order by t.PublishDate desc

score 0 · Answer 4 · answered Nov 23 '18 at 09:17

This is what I ended up with thanks to @Damien_The_Unbeliever and @Mayank Porwal

Select TOP(10) *
FROM
(
SELECT News.Author, News.CreateDate, News.CustomerId, News.EditDate, News.EndDate, News.Id, News.ImageUrl, News.PublishDate, News.Text, News.Title
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (1)

EXCEPT

SELECT News.Author, News.CreateDate, News.CustomerId, News.EditDate, News.EndDate, News.Id, News.ImageUrl, News.PublishDate, News.Text, News.Title
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (2)

) a

ORDER BY a.PublishDate DESC

That's great. But mark the appropriate responses as answers since that how SO is designed to work - questions are asked and then **answered** so that others having the same or similar question can find an **answer**. — SMor, Nov 24 '18 at 13:22

Nikhil S · Answer 5 · 2018-11-23T10:04:17.170

this will work:

SELECT TOP 10 * from  (SELECT *
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (1)

EXCEPT

SELECT *
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (2)

ORDER BY News.PublishDate DESC);

SQL Query Except then select TOP 10

5 Answers5