CODEWITHSUNDEEP
sqlsql-servert-sqlpagination
notAnonymousAnymore
notAnonymousAnymore

Reputation: 2687

Complex SQL pagination Query

I am doing pagination for my data using the solution to this question.

I need to be using this solution for a more complex query now. Ie. the SELECT inside the bracket has joins and aggregate functions.

This is that solution I'm using as a reference:

;WITH Results_CTE AS
(
    SELECT
        Col1, Col2, ...,
        ROW_NUMBER() OVER (ORDER BY SortCol1, SortCol2, ...) AS RowNum
    FROM Table
    WHERE <whatever>
)
SELECT *
FROM Results_CTE
WHERE RowNum >= @Offset
AND RowNum < @Offset + @Limit

The query that I need to incorporate into the above solution:

SELECT users.indicator, COUNT(*) as 'queries' FROM queries
INNER JOIN calls ON queries.call_id = calls.id
INNER JOIN users ON calls.user_id = users.id
WHERE queries.isresolved=0 AND users.indicator='ind1'
GROUP BY users.indicator ORDER BY queries DESC

How can I achieve this? So far I've made it work by removing the ORDER BY queries DESC part and putting that in the line ROW_NUMBER() OVER (ORDER BY ...) AS RowNum, but when I do this it doesn't allow me to order by that column ("Invalid column name 'queries'.").

What do I need to do to get it to order by this column?

edit: using SQL Server 2008

Upvotes: 0

Views: 776

Answers (2)

Rashmi Kant
Rashmi Kant

Reputation: 159

I think queries your alias name for count(*) column then use like this

SELECT users.indicator, COUNT(*) as 'queries' FROM queries
INNER JOIN calls ON queries.call_id = calls.id
INNER JOIN users ON calls.user_id = users.id
WHERE queries.isresolved=0 AND users.indicator='ind1'
GROUP BY users.indicator ORDER BY COUNT(*) DESC

http://oops-solution.blogspot.com/2011/11/string-handling-in-javascript.html

Upvotes: 0

dmn
dmn

Reputation: 94

Try ORDER BY COUNT(*) DESC . It works on MySQL ... not sure about SQL Server 2008

Upvotes: 1

Related Questions