Question posted in
The official documentation can be found here.

Redis – Query failed — select count(*) with order by got sqlserver error 8127

Suppose I have some data in TableA like below

ID  TIME                    VAL
1   2020-10-01 12:00:12.000 test
2   2020-10-01 13:00:12.000 test2
3   2020-10-01 13:00:12.000 test2
4   2020-10-01 13:00:12.000 test2
5   2020-10-01 13:00:12.000 test2
6   2020-10-01 13:00:12.000 test2
7   2020-10-01 13:00:12.000 test2
8   2020-10-01 13:00:12.000 test2
9   2020-10-01 13:00:12.000 test2

I want to count the data with order by time and limit offset, I tried like this

SELECT COUNT(*)
FROM TableA
WHERE TIME>'2020-10-01 00:00:00' AND TIME<'2020-10-01 23:59:59'
ORDER BY TIME ASC OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY

but I get an error

Column "TableA.TIME" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

Executed the query under SQL Server 2012

how to make it right?

the result why i do this process

I want count one page’s records and check as if equals the page size. then decide to query the real data again and cache them.

suppose the data has been added by time one by one, i want count then data with page by page, suppose one page include 50 records. so at first i have to order by time asc, then get the count of target page by ‘offset limit’, while the count is equals the redis cached data’s count, i will get the data from redis, if not, which means these is some new data inserted, i will get the data from db, then cache the new count and new data into redis again.
all the data should be order by time asc with pagenation,which means the last page’s count will change,so i need do such query to check the actural count of one page.

Tags:

2

Answers


  1. 0

    The start of your query makes us think that you want to get a single output which is a count of all the items in your table between the two dates:

    SELECT COUNT(*)
FROM TableA
WHERE TIME>'2020-10-01 00:00:00' AND TIME<'2020-10-01 23:59:59'

    But the rest of the query makes us think that you want to get the first 50 elements of the filtered elements:

    ORDER BY TIME ASC OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY

    If you want to count, keep only the first part:

    SELECT COUNT(*)
FROM TableA
WHERE Time BETWEEN '2020-10-01 00:00:00' AND '2020-10-01 23:59:59'

    If you want to get the results, remove the count:

    SELECT *
FROM TableA
WHERE Time BETWEEN '2020-10-01 00:00:00' AND '2020-10-01 23:59:59'
ORDER BY TIME ASC OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY
    Login or Signup to reply.
  2. 0

    I think you can do this as:

    SELECT COUNT(*)
    from(
    select *
    FROM TableA
    WHERE TIME>'2020-10-01 00:00:00' AND TIME<'2020-10-01 23:59:59'
    ORDER BY TIME ASC OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY
    ) as x
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search