SQL Query Modification

Question

Post reply

SQL Query Modification

sathishkm

SSC Veteran

Points: 239
More actions
May 11, 2023 at 8:31 am

#4187832

Hi All,
I need the below query to be modified to get the results of 100 records. As of now I get only 1 result.

SELECT
CASE WHEN CHARINDEX(' ', concatenated_column) = 0 THEN concatenated_column ELSE SUBSTRING(concatenated_column, 1, CHARINDEX(' ', concatenated_column) - 1) END AS first_common_word,
CASE WHEN CHARINDEX(' ', concatenated_column, CHARINDEX(' ', concatenated_column) + 1) = 0 THEN NULL ELSE SUBSTRING(concatenated_column, CHARINDEX(' ', concatenated_column) + 1, CHARINDEX(' ', concatenated_column, CHARINDEX(' ', concatenated_column) + 1) - CHARINDEX(' ', concatenated_column) - 1) END AS second_common_word
FROM (
SELECT
(SELECT Description + ' ' FROM RawDataDescriptions FOR XML PATH('')) AS concatenated_column
) AS t
WHERE concatenated_column LIKE '% %'
ORDER BY LEN(concatenated_column) - LEN(REPLACE(concatenated_column, ' ', '')) DESC
OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY;

May 17, 2023 at 11:18 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

Just to get you back on track ...

How many rows do you expect from your nested select(s) ?

SELECT Description + ' ' FROM RawDataDescriptions FOR XML PATH('')) AS concatenated_column

and why are you using "for xml path" in this query ?

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

sathishkm SSC Veteran Points: 239 More actions · Answer 2

sathishkm

SSC Veteran

Points: 239

May 11, 2023 at 9:23 am

#4187849

100 results

sathishkm SSC Veteran Points: 239 More actions · Answer 3

I want to find the top 100 1st words and 2nd second words in a cell in a column

Steve Collins SSCrazy Points: 2157 More actions · Answer 4

This is a tricky query. You have 2 separate aggregations for which the results are then LEFT JOIN'ed by rank. What I'm not understanding in my own code is why the WHERE clause in the 3rd CTE is necessary. Hunh? It seems like it should work without the line:

    where word2 is not null

But it counts the nulls for some reason. Afaik this code returns the correct results

drop table if exists #title_one_space;
go
select * into #title_one_space from (values
 ('one word')
,('one word two words')
,('one word two words three words')
,('two')
,('two wood two words')
,('two wind two words three words')
,('tree')
,('car wood two words')
,('tree wood two words three words')
,('four word two words three words four')) v(title);

with
words_cte as (
    select *
    from #title_one_space tos
         cross apply (values (charindex(' ', tos.title))) v(ndx_spc1)
         cross apply (values (iif(v.ndx_spc1=0, title, substring(title, 1, v.ndx_spc1-1)))) frst(word1)
         outer apply (values (nullif(substring(tos.title, v.ndx_spc1+1, 
                                     charindex(' ', tos.title, v.ndx_spc1-len(frst.word1))), ''))) scnd(word2)),
frst_cte as (
    select top(100) word1, count(*) w1_count, row_number() over (order by count(*) desc) rn
    from words_cte
    group by word1
    order by count(*) desc),
scnd_cte as (
    select top(100) word2, count(*) w2_count, row_number() over (order by count(*) desc) rn
    from words_cte
    where word2 is not null
    group by word2
    order by count(*) desc)
select frst.rn, frst.word1, frst.w1_count, scnd.word2, scnd.w2_count
from frst_cte frst
     left join scnd_cte scnd on frst.rn=scnd.rn;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 5

100 results is incorrect !

due to your query:

SELECT Description + ' ' FROM RawDataDescriptions FOR XML PATH(''))

it produces 1 row !

test it with the data of Steves example:

SELECT title + ' '
FROM #title_one_space FOR XML PATH('');

How many rows?