Select Grouped Record 2

[Iamthestig]

We have some corrupt data in our tblCompanyHistory table.
I need to find the records Grouped by CREF that have a comp_order not starting with 1.

Here is some data.

CREF Comp_Order
--------------------
8 1
8 2
8 3
9 2
9 3
10 1
11 3
11 4

So CREF 8 and 10 are valid but 9 and 11 are not.
The Comp_Order should always begin with 1.

I think I need to use MAX, I am going to look into it but
any help much appreciated.
Thanks

 

[PWise]

Try

Select cref
from tablename
left join
(Select Cref
From Tablename
where Comp_Order =1)GoodData
on gooddata.cref=tablename.cref
where gooddata.cref is null

 

[fxthoth]

Hi,
try this:

declare @myTable table(
	col1 int,
	col2 int
)

insert into @myTable values (8,1)
insert into @myTable values (8,2)
insert into @myTable values (8,3)
insert into @myTable values (9,2)
insert into @myTable values (9,3)
insert into @myTable values (10,1)
insert into @myTable values (11,3)
insert into @myTable values (11,4)

select min(col2) as cref, col1 from @myTable
group by col1
having min(col2)=1

returns:
cref col1
----------- -----------
1 8
1 10

select min(col2) as cref, col1 from @myTable
group by col1
having min(col2)>1

returns:
cref col1
----------- -----------
2 9
3 11

 

[gmmastros]

Another way...

I'm only putting this here because I spent the time creating this query.

Select Cref
from   TableName
Group By CREF
Having Count(Case When Comp_Order = 1 Then 1 End)[!] = 1[/!]

With =1, you will get the valid CREF's. Change it to =0 and you will get the invalid ones instead.


-George

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

 

[mutley1]

Isn't this a bit simpler or did I miss something (It's late here.......!!)

select *
from tblCompanyHistory
where cref not in
		(select cref from tblCompanyHistory
		where comp_order = 1)

Cheers,

M.

 

[mutley1]

Sorry George - didn't mean simpler than yours (I'll never reach your standards!!), just had the page open and hadn't posted when you had!

Rgds,

M.

 

[Iamthestig]

Thanks guys, much appreciated.

Pwise and Mutley, your answers both gave the right answer but all were educational.

Thanks, again.