Excluding data on query - SQL Server Forums

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 2012 Forums

Transact-SQL (2012)

Excluding data on query

Author

Topic

dhpeter
Starting Member

3 Posts

Posted - 2014-07-22 : 17:45:25

Hi,

I am very new to SQL and may have a hard time explaining what i want in a way that makes sense - here goes.

I am using a sample database with the table titleauthors

columns au_id, title_id, au_ord

If there are multiple au_id for title_id they are ordered by au_ord

What i am asking is if au_ord (numbers 1-3) is equal to 3 i want it excluded from the results completely

IE

Au_ID title_ID au_ord
12345 1234 1
11111 1234 2
22222 1234 3
33333 5555 1

What i am asking is for it to remove the rows where if au_ord > 2 it would remove all the title_id that has an au_ord > 2

so the results would just show the bottom row.

Am i making sense?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-22 : 17:47:28

http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-07-22 : 18:13:56

[code]SELECT w.*
FROM (
SELECT Title_ID
FROM dbo.TitleAuthors
GROUP BY Title_ID
HAVING MAX(Au_Ord) <= 2
) AS w
INNER JOIN dbo.TitleAuthors AS ta ON ta.Title_ID = w.Title_ID;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

dhpeter
Starting Member

3 Posts

Posted - 2014-07-22 : 18:51:05

quote:
Originally posted by SwePeso

SELECT w.*
FROM (
SELECT Title_ID
FROM dbo.TitleAuthors
GROUP BY Title_ID
HAVING MAX(Au_Ord) <= 2
) AS w
INNER JOIN dbo.TitleAuthors AS ta ON ta.Title_ID = w.Title_ID;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Thank you! I'm just entering sub-queries in my book

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources