less than 2 items - 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

General SQL Server Forums

New to SQL Server Programming

less than 2 items

Author

Topic

gongxia649
So Suave

344 Posts

Posted - 2006-05-04 : 21:18:30

t1
order
item

how can i get the order that has less than 2 items?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-04 : 21:22:09


select order, count(*)
from   t1
group by order
having count(*) < 2

gongxia649
So Suave

344 Posts

Posted - 2006-05-04 : 21:53:39

if i want to delete how will i do it?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-04 : 22:08:51

Just inner join it with the table t1.


delete d
from   t1 d inner join
(
    select order, count(*) as cnt
    from   t1
    group by order
    having count(*) < 2  
) t
on  d.order = t.order