select rows with conditions then the rest in the same query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

select rows with conditions then the rest in the same query?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-08-08 : 07:43:14

Thomas writes "Hello I have a database that contains the following tables:

category category_id, category_name

news news_id, news_heading ...

news_category news_id, category_id

What I am trying to do is select 10 news, but select first the ones that contain a certain word in the heading (news_heading), then the rest of the news.

A query to get the first 10 results would be the following:

SELECT top 10 * FROM dhn_news a, dhn_news_category b WHERE
a.News_ID = b.News_ID AND b.Category_ID = 430012480 ORDER BY a.News_Date DESC

It misses the selection of the news with the keyword 'keyword' in news_heading.

For instance if there were 3 news stories with the keyword in the database, it would show those 3, then 7 other results not containing the keyword; if there were none in the first selection, it would still show 10 results.

If it's possible, the second set of results would not contain any results that are in the first one...

In fact the main problem is that I want to use a single sql statement for that, if it's possible?

We're running windows server 2003, standard edition and sql server."

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-08-08 : 09:52:21

Try:

SET ROWCOUNT 10

SELECT *
FROM   dhn_news AS n
JOIN   dhn_news_category AS nc
ON     n.News_ID = nc.News_ID
WHERE  n.news_heading LIKE '%keyword%'
UNION ALL
SELECT *
FROM   dhn_news AS n
JOIN   dhn_news_category AS nc
ON     n.News_ID = nc.News_ID
WHERE  n.news_heading NOT LIKE '%keyword%'
ORDER BY News_Date DESC

SET ROWCOUNT 0

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources