This content has been marked as final. Show 7 replies
you can try something like this,
SELECT id, COUNT(*) FROM Table1
WHERE package = 'TV'
GROUP BY id
HAVING COUNT(*) < 4;
it works. thanks a lot.
Sorry, it also select package number>4, it's wrong.
Anyone could give me some idea?
Post the data on which your are trying to query, query you are trying and the result you are getting.
where id in (select id
where package = 'TV')
group by id
having count(*) < 4;
Now, it's right.
How do i list their packages?
select id,packagesimilar thread
from (select id,package,
max(case when package = 'TV'
else 0 end) over(partition by id) as hasTV,
count(*) over(partition by id) as RecordCount
where hasTV = 1
and RecordCount < 4
order by id,package;
Select only records with same Id