How to load specific rows 100 at a time

Question

I am trying to search every 100 record so the first bulk will be 1 to 100 second will be 101 to 200 etc.
I am also using joins to combine two tables.
When I execute the query I get error saying: The column 'ID' was specified multiple times for 'cte'.

This is my query:

WITH cte AS (
    SELECT
          ROW_NUMBER() OVER (
            ORDER BY TableOne.Name
          ) AS ROW
        , *
    FROM
      DatabaseNameOne
      FULL JOIN DatabaseNameOne
      ON DatabaseNameTwo.ID= DatabaseNameOne.ID
    WHERE CONVERT(DATE,DatabaseNameOne.dateone) BETWEEN '2013-12-01' AND '2014-05-20'
)

SELECT
    *
FROM
    cte
WHERE
    ROW BETWEEN '1' AND '100'

Can some one tell me what I am doing wrong here?

I am using server 2008. I can select 1 to 100 record with out join, but soon as I join a table I get this error

Answer 1

This is your cte:

WITH cte AS (
       SELECT ROW_NUMBER() OVER(ORDER BY TableOne.Name) AS ROW,
              *
       FROM DatabaseNameOne FULL JOIN
            DatabaseNameOne
            ON DatabaseNameTwo.ID = DatabaseNameOne .ID
       WHERE convert(date, DatabaseNameOne.dateone) between '2013-12-01' and '2014-05-20'
     )

You are joining a table to itself and using * -- all the columns are duplicated. Even if the tables are different, the join is on an ID field, so that column will be duplicated. A CTE/subquery/table cannot have duplicate names? How would SQL Server know which column you are referring to in a reference?

Instead, you need to list the specific columns you want from the join.

Answer 2

I am going to assume you meant to join to DatabaseNameTwo and that each table has three fields. What your current cte is doing if you expand the select *:

WITH cte AS (
    SELECT
          ROW_NUMBER() OVER (
            ORDER BY TableOne.Name
          ) AS ROW
        , DatabaseNameOne.id,  DatabaseNameOne.SomeOtherField,  DatabaseNameOne.YetAnotherField, 
         DatabaseNameTwo.id, DatabaseNameTwo.AnotherField,  DatabaseNameTwo.HowManyMoreFieldsAreThere
   FROM
      DatabaseNameOne
      FULL JOIN DatabaseNameOne
      ON DatabaseNameTwo.ID= DatabaseNameOne.ID
    WHERE CONVERT(DATE,DatabaseNameOne.dateone) BETWEEN '2013-12-01' AND '2014-05-20'
)

As you can see there are two columns with teh name ID. That is what si scauseing teh error.

Since both IDs are the same, you only need to list one (one of the many reasons why you should never use select * is the repetition of data that is wasteful of precious database and network resources when you have joins). So this should work (After you change to the real column names and table names and fix the select * in the final query which I was too lazy to do):

WITH cte AS (
    SELECT
          ROW_NUMBER() OVER (
            ORDER BY TableOne.Name
          ) AS ROW
        , DatabaseNameOne.id,  DatabaseNameOne.SomeOtherField,  DatabaseNameOne.YetAnotherField, 
         DatabaseNameTwo.AnotherField,  DatabaseNameTwo.HowManyMoreFieldsAreThere
   FROM
      DatabaseNameOne
      FULL JOIN DatabaseNameOne
      ON DatabaseNameTwo.ID= DatabaseNameOne.ID
    WHERE CONVERT(DATE,DatabaseNameOne.dateone) BETWEEN '2013-12-01' AND '2014-05-20'
)

SELECT
    <List the fies here)
FROM
    cte
WHERE
    ROW BETWEEN '1' AND '100'