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/
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -