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

1

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

2

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

3

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

4

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,
         dateadd(dd,ac1.column_id, getdate())   sba_date,
		 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
5

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'),
('Avocado'),
('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'),
('Avocado'),
('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'),
('Avocado'),
('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'),
('Avocado'),
('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'),
('Avocado'),
('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'),
('Avocado'),
('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'),
('Avocado'),
('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'),
('Avocado'),
('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'),
('Avocado'),
('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'),
('Avocado'),
('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'),
('Avocado'),
('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'),
('Avocado'),
('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'),
('Avocado'),
('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'),
('Avocado'),
('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'),
('Avocado'),
('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'),
('Avocado'),
('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'),
('Avocado'),
('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'),
('Avocado'),
('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'),
('Avocado'),
('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'),
('Avocado'),
('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'),
('Avocado'),
('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'),
('Avocado'),
('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'),
('Avocado'),
('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'),
('Avocado'),
('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'),
('Avocado'),
('Blackberry'),
('Apricot'),
('Banana'),
('Cucumber'),
('Date'),
('Lemon'),
('Lime'),
('Nectarine'),
('Orange');

6

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

7

How's that?

8

See my example of creating and populating data

9

Scott

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

10

Hi MapMaker

What you put .. is good ..

It is the create table .. and insert table script !!
:+1:

11

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'),
('Avocado'),
('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'),
('Avocado'),
('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'),
('Avocado'),
('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'),
('Avocado'),
('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'),
('Avocado'),
('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'),
('Avocado'),
('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'),
('Avocado'),
('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'),
('Avocado'),
('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'),
('Avocado'),
('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'),
('Avocado'),
('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'),
('Avocado'),
('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'),
('Avocado'),
('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'),
('Avocado'),
('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'),
('Avocado'),
('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'),
('Avocado'),
('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'),
('Avocado'),
('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'),
('Avocado'),
('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'),
('Avocado'),
('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'),
('Avocado'),
('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'),
('Avocado'),
('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'),
('Avocado'),
('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'),
('Avocado'),
('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'),
('Avocado'),
('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'),
('Avocado'),
('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'),
('Avocado'),
('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
image

12

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?

13

This is a microsoft sql server forum

You might get a better answer on mysql website

14

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?

15

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

image

16

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

17

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

18

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