Thanks a lot for the suggestion. It works and it didn't effect the sql
execution plan.
Prakash
"Mike" <nospam_at_nowhere.net> wrote in message news:<VuAe8.6$nA1.14469_at_vicpull1.telstra.net>...
> You could try this:
>
> Select
> count(*) Match_count
> , sku
> , decode(sum(decode(sign(instr(ph.desc,'vcr')),0,0,1)),0,null,'vcr ') ||
> decode(sum(decode(sign(instr(ph.desc,'tape')),0,0,1)),0,null,'tape ') ||
> decode(sum(decode(sign(instr(ph.desc,'video')),0,0,1)),0,null,'video')
> words_matched
> from product_header ph
> where (ph.desc = 'vcr' or ph.desc = 'tape' or ph.desc = 'video')
> group by sku
> order by match_count desc
>
> Not very nice when its hard coded through.
>
> Mike
>
> "Prakash Pattisapu" <lalithprakash_at_hotmail.com> wrote in message
> news:d11ffcbb.0202250905.4ae3c9eb_at_posting.google.com...
> > Hi,
> > Is there a way to include group and non group columns in the same
> > sql?.
> > I have the following sql..
> >
> > Select count(*) Match_count, sku from product_header ph
> > where (ph.desc = 'vcr' or ph.desc = 'tape' ph.desc = 'video')
> > group by sku
> > order by match_count desc
> >
> >
> > The above query will give me the number of occurences of the words
> > 'vcr', 'tape' and 'video' for each sku.
> > ex: match_count sku
> > 3 100
> > 2 101
> > 2 102
> > 1 103
> >
> > My problem is, i also want the "words" that matched in the result set.
> > Like this...
> > ex: match_count sku words_matched
> > 3 100 vcr tape
> > 2 101 vcr video
> > 2 102 tape video
> > 1 103 tape
> >
> > Since, ph.desc is not a group by column, i can't include it in the sql
> > directly.
> > Is there a way to achieve the above result set without looping thru
> > the outer query (The main group by query). To put it straight, can we
> > do this in one sql?.
> > Thanks so much,
> > Prakash
> >
> >
> > select count(*)
Received on Thu Mar 07 2002 - 09:39:25 CST
