Find number of times value is > 1 2

[leveetated]

This sounds simple - and perhaps it is - but I cannot seem to wrangle a query around this.

I have a table like this:

id     displayorder
---    ------------
71398	1
81545	1
81545	2
81545	1
71398	2
11620	1
11620	2
11620	3

...and I simply want to select the rows where a given id has a displayorder value of > 1, but no displayorder =1 for that given id.

The solution is probably SO obvious but no matter what I write it's not excluding cases where a given id also has a displayorder of 1.

Thanks so much for any pointers.

Best,

Lee.

 

[markros]

select * from myTable T1 where DisplayOrder > 1 and not exists (select 1 from myTable T where DisplayOrder = 0 and T.PK = T1.PK)

 

[markros]

select ID from myTable T1 where DisplayOrder > 1 and not exists (select 1 from myTable T where DisplayOrder = 1 and T.ID = T1.ID)

 

[gmmastros]

Try this:

select   Id, Min(DisplayOrder)
from     YourTableNameHere
Group By Id
Having   Min(DisplayOrder) > 1

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Assuming DisplayOrder is always positive, of course.

 

[leveetated]

Thank you markros and gmmastros for your suggestions. I am sorry I left out one "AND" condition I should have mentioned in my original question! I guess that made all the difference. But after using your queries as a start I was able to finally write one that worked - here's the query that I used to generate a db view for this:

SELECT TOP (100) PERCENT DisplayOrder, ID
FROM dbo.myTable
WHERE (ID IN
(SELECT ID FROM dbo.myTable AS myTable_2
WHERE (DisplayOrder > 1) AND (typeID = 1))) AND (ID NOT IN
(SELECT ID FROM dbo.myTable AS myTable_1
WHERE (DisplayOrder = 1) AND (typeID = 1))) AND (DisplayOrder > 1)
ORDER BY ID

Many thanks for giving me the jumpstart to the brain that I needed.

Best,

Lee.

 

[markros]

Was it auto-generated and you can not change it? Otherwise I would rather use mine or George's answer instead since yours made it the less optimizable.

 

[leveetated]

Yes, that was the auto-generated query, courtesy of MS SQL Server 2005.

Thank you,

Lee.