SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Select top 1 with like ...? inline or SP?		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dainova
Starting Member

29 Posts
Posted - 11/12/2012 :  14:13:00  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
5469 Posts
Posted - 11/12/2012 :  14:24:34  Show Profile  Reply with Quote
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
Edited by - TG on 11/12/2012 15:23:54
Go to Top of Page

dainova
Starting Member

29 Posts
Posted - 11/12/2012 :  17:21:29  Show Profile  Reply with Quote
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
Edited by - dainova on 11/12/2012 17:30:45
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000