# How do I... read a table in a special way?

All:
I have a table of 1000 records and I need to read every 5th record in an "iteration" of 10 records (i.e. 5,15,25,35,45...995, etc.)

Each "iteration" consists of reading 10 words (records), so, in this case of 1000 words, there would be 100 iterations.

How can I accomplish this, while at the same time counting the number of times a record repeats itself as the 5th word in each iteration? For example, counting the number of times the word "Apple" is found each 5th occurrence of each iteration? I only want the word which is found the most times in the 5th slot of 100 iterations.

Eventually, I also want to do this for each 1st word (step 10, so it would be 1, 11, 21, 31, 41, 51, etc.), each 2nd word (2, 12, 22, 32, 42, etc.), each 3rd word, each 4th word, etc., etc. (iterations 1-10) . The end result will be the most popular word used in each of the 10 iterations. i.e The most popular word read in the 1st position, 2nd, position, 3rd position, etc.

A "top 10" list if you will, will be produced...

Anybody got any ideas (SQL code, algorithm, etc.) how to do this in SQL? I hope I've explained it well.

As always, your HELP is greatly appreciated...
Perplexed in Pittsburgh

Please define every fifth record? randomly or because the data is sorted a specific way?

Randomly... the 5th occurrence could contain several fruit names, and I want the most popular one from the 5th slot

Please provide sample data of at least 20 rows. look at this sample code and data

``````use sqlteam
go

create table #corn(res_number int, subres_number int ,
sba_date date, room_number int)

insert into #corn
SELECT TOP 100000 ac1.column_id as res_number,
ABS(CHECKSUM(NEWID())%10000)+10000 subres_number,
ac1.object_id room_number
FROM      sys.all_columns ac1
CROSS JOIN sys.all_columns ac2

;with cte as
(
select ROW_NUMBER() over( order by (select null)) as fifth_row  , *
from #corn
),
fifth_row as
(
select ROW_NUMBER() over( order by (select null)) as row_num,*
from cte
where fifth_row%5=0
)
select *
from fifth_row

drop table #corn

``````

Create table fruit(name varchar(10));

INSERT INTO fruit(name)
VALUES
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange');

Nice, long sample of data, but we need it as CREATE TABLE and INSERT statements, not just a "splash" of data on the screen.

How's that?

See my example of creating and populating data

Scott

I believe .. that Long Statement is the create table and insert data into table ..

Hi MapMaker

What you put .. is good ..

It is the create table .. and insert table script !!

hi

i tried to do this !!

please check if its ok !!

please click arrow to the left for drop create DATA
``````drop table fruit
go

Create table fruit(name varchar(10));
go

INSERT INTO fruit(name)
VALUES
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange'),
('Apple'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Pineapple'),
('Cherry'),
('Blueberry'),
('Grape'),
('Watermelon'),
('Kiwi'),
('Peach'),
('Pear'),
('Grape'),
('Watermelon'),
('Strawberry'),
('Peach'),
('Pear'),
('Grape'),
('Grape'),
('Watermelon'),
('Guava'),
('Pineapple'),
('Grapefruit'),
('Fig'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange');
go
``````
``````; with cte_rn as
(
select ROW_NUMBER() over(order by (select null))  as rn , *  from  fruit
)  , rn_5 as
(
select *  from  cte_rn   where rn%5 = 0
)
select top 1 with ties  name ,count(*) from  rn_5 group by  name order by  count(*) desc

go
``````

the fruit that occurs the most in all the 5th records

What flavor of db engine is this written for?
I have MySQL, so how would I modify this to work in MySQL?

Additionally, any suggestions on how to do with a query that finds the 1st (not just the 5th), 2nd, 3rd, 4th, ...10th in all 100 iterations with just one query?

This is a microsoft sql server forum

You might get a better answer on mysql website

hi

doing the SQL Looks very easy

can you please explain what you mean by this !!!!
not able to understand !!!!

Additionally, any suggestions on how to do with a query that finds the 1st (not just the 5th), 2nd, 3rd, 4th, ...10th in all 100 iterations with just one query?

hi is this what you mean by
Additionally, any suggestions on how to do with a query that finds the 1st (not just the 5th), 2nd, 3rd, 4th, ...10th in all 100 iterations with just one query?

``````; with cte_rn as
(
select ROW_NUMBER() over(order by (select null))  as rn , *  from  fruit
)  , rn_5 as
(
select 'rn5' as rn_name,*  from  cte_rn   where rn%5 = 0
)  , rn_4 as
(
select 'rn4'as rn_name,*  from  cte_rn   where rn%4 = 0
) , rn_3 as
(
select 'rn3' as rn_name,*  from  cte_rn   where rn%3 = 0
)
, rn_2 as
(
select 'rn2' as rn_name,*  from  cte_rn   where rn%2 = 0
)
, rn_1 as
(
select 'rn1'as rn_name,*  from  cte_rn   where rn%1 = 0
)
select * from
(
select top 1 with ties  rn_name,name as name,count(*) cnt from  rn_5 group by  rn_name,name order by  count(*) desc
union all
select top 1 with ties  rn_name,name ,count(*) from  rn_4 group by  rn_name,name order by  count(*) desc
union all
select top 1 with ties  rn_name,name as name,count(*) cnt from  rn_3 group by  rn_name,name order by  count(*) desc
union all
select top 1 with ties  rn_name,name ,count(*) from  rn_2 group by  rn_name,name order by  count(*) desc
union all
select top 1 with ties  rn_name,name ,count(*) from  rn_1 group by  rn_name,name order by  count(*) desc
) a
``````

Yes, I believe that's correct, but I do NOT have SQL Server to test this. I have MySQL or SQLLite

Please see if this code works for you !!! testing .. in MySQL , SQLLite

the things that may need to .. CONVERTED to equivalent are ( or look for alternate approaches )

-- CTE common table expressions ( cte , rn_1 , rn_2 etc )
-- Top 1
-- Union All

It is now, before it was just a long list of names:
Apple
Strawberry
etc.