Select DISTINCT records but ordered by not selectable column

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

SQL Server Development (2000)

Select DISTINCT records but ordered by not selectable column

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-06-29 : 08:06:13

Angel writes "Hi, I have the following problem:

I have tables Publications and Categories. One Publication can be posted in many categories.

I want to get the DISTINCT IDs of all publications from specified set of categories orded by their creation date:


SELECT DISTINCT Publications.ID FROM Publications 
INNER JOIN PublicationsCategories ON (PublicationsCategories.PublicationID=Publications.ID)
WHERE 
     (PublicationsCategories.CategoryID IN [2,4,15])
ORDER BY Publications.DateCreated

The point is that I need only the Publication IDs in the selected columns but DateCreated shoud be there also because of the "ORDER BY with DISTINCT" condition.

What query I can use to get that? To get DISTINCT IDs but records but sorded by date?

(I need this for SQL paging by cursor.)

Thanks in advance. Best regards!"

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2005-06-29 : 10:45:41

[code]
SELECT ID
FROM Publications
where exists (select *
from PublicationsCategories
where PublicationsCategories.PublicationID = Publications.ID
and CategoryID in (2,4,15))
ORDER BY DateCreated
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources