October 28, 2006 at 4:49 pm
Hi SQLers,
Table1:
NameField DataField
Joe zzzz
Fred sss
Joe syz
Lou aaa
Bob abye
Joe qwe
Fred ccc
I would like to return top X records. I define 'top' as 'the most recurring' in this case of the NameField.
Say top 1 - this would be the Joe records
Or top 2 - would be Joe and Fred records
(there is a key field and other data fields not showing).
If top 3 then Joe, Fred - and since Bob & Lou are tied for 3rd then not sure what is returned - maybe both...
Have been thumbing thru my sql book but not there and am pretty light in experience. Would welcome help on this syntax & methods...thnx
October 28, 2006 at 7:45 pm
I hope you are looking for this query
SELECT TOP 100 WITH TIES NameField
FROM table
GROUP BY NameField
ORDER BY COUNT(NameField) DESC
October 30, 2006 at 9:22 am
no no - sorry but thanks for the effort....this returns only the single name of the most recurring name i.e. if I use your code with TOP 1 it will return only the name: Joe
What I need is for all the Joe records to be returned - including all data fields.
regards....
October 30, 2006 at 9:49 am
Got the answer from another site....for anyone who reads this and has the same need...the following works great:
SELECT *
FROM Table1 As Main
WHERE Main.NameField In
( SELECT TOP 1 Sub.NameField
FROM Table1 As Sub
GROUP BY Sub.NameField
ORDER BY Count(Sub.NameField) DESC); Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply