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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Select top 1 with like ...? inline or SP?

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 t
join A a on a.STR1 = substring(t.cmsg,5,10)


EDIT:
reposted - I forgot to include the "top 3" thing

Be One with the Optimizer
TG
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -