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.
Author |
Topic |
dainova
Starting Member
31 Posts |
Posted - 2012-11-12 : 14:13:00
|
What I'm trying to achieve:Find sample entries from table having most occurences in #temp(top 3), I group by column <cmsg> that can have many diff values (it's lor file error description),so I'm grouping it by except take from the middle, bypassing timestamps (e.g. substring(cmsg,15,20) = 'ERROR Platform.Information').I was thinking to do this with simple Select but stuck, I face now the case that I need to use <LIKE IN (list1,list2) and it won't work?Should I code some kind of loop or it's still possible with select. I'm kind of limited in my resources re defining sp or functions, but I still can do script with cursors, etc??. I don't think I can do this with Join on partial column?so psedo logic would be something like this:--select A:select top 3 a.* from (select count(*) as CC1, substring(cmsg,15,20) as STR1 from #temp group by substring(cmsg,5,10) ) a order by CC1 desc-- 120 | ERROR Platform1-- 115 | ERROR Platform2-- 78 | Error Page1 --Select B:for i in (1,2,3) do select * from #temp where cmsg like (select #i from select A) -- 1 | server1 | 2011-11-22 | 14:04:33 ERROR Platform1 failure | other fields -- 2 | server1 | 2011-11-23 | 11:04:33 ERROR Platform2 problem| other fields-- 3 | server1 | 2011-11-22 | 09:04:33 Error Page1 null.. | other fields |
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2012-11-12 : 14:24:34
|
Perhaps this?;with A (CC1, STR1) as( select top 3 count(*) , substring(cmsg,15,20) from #temp group by substring(cmsg,5,10) order by count(*) desc) select t.*from #temp1 tjoin A a on a.STR1 = substring(t.cmsg,5,10) EDIT:reposted - I forgot to include the "top 3" thingBe One with the OptimizerTG |
|
|
dainova
Starting Member
31 Posts |
Posted - 2012-11-12 : 17:21:29
|
Thanks, TG,I think this still will brings me multiple records for each group = substring(t.cmsg,15,10), so I need one more group by probably |
|
|
|
|
|
|
|