How to get max for group by data

Declare @str varchar(50)
set @str=''
declare @str2 varchar(50)
set @str2=''

declare @i int
set @i=0
while(@i<=1000000000000)
begin
 SELECT  top 6 @str=@str+[Number]+',' FROM [Infosys_Connie].[dbo].[DoubleBall]
 order by newid()
 print @str
 SELECT  top 1 @str2=Number FROM [Infosys_Connie].[dbo].[DoubleBall] where [Number]<=16 order by newid()
    INSERT INTO [Infosys_Connie].[dbo].[PrizeNumber]
           ([RedNumber]
           ,[BlueNumber]
           ,[TotalNumber])
     VALUES
           (@str,
           @str2,
           @str+@str2)
 set @i=@i+1
 set @str=''
end

 

SELECT * FROM
[PrizeNumber] a
where a.BlueNumber
=
(
 select c.MBlueNumber
 from
 (
  select top 1 BlueNumber as MBlueNumber, count(*) as con from [PrizeNumber] b
  group by b.BlueNumber order by con desc
 )  c
)

order by a.TotalNumber asc

转载于:https://www.cnblogs.com/zhangchenliang/archive/2010/03/17/1688185.html