Database

select * from
(select userid,username from temptable where userid=1 group by userid,username)as a
inner join 
(select userid, count as cnt from temptable where score>21 
group by userid)as b on a.userid=b.userid

select * from
(select userid,username from temptable where userid=1 
group by userid,username)as a
left join (select userid, count(*)as cnt from temptable 
where score>21 group by userid)as b on a.userid=b.userid

CASE cnt when NULL then 0 END

SELECT DISTINCT userid
      ,username
      ,(SELECT Count(*) FROM temptable t2 WHERE t1.userid=t2.userid AND score > 20) as  topscorecount
FROM temptable t1
WHERE userid = 1

azad yadav wrote:

write a query that produced single row find the max form two table

select max(twotable.fieldmatch) from 
(
select max(field1) as fieldmatch from table1
UNION
select max(field1) as fieldmatch from table2
)twotable

azad yadav wrote:

query that delete all redundant data from table in sql 2005