2

I have the following

SELECT results.*, coinCat.*, cat.* 
FROM (SELECT c.*, ROW_NUMBER() OVER (ORDER BY c.CoinId) AS RowNumber 
      FROM coins c) AS results
INNER JOIN CoinCategoriesCategories coinCat ON coinCat.CoinId = results.CoinId
INNER JOIN CoinCategories cat ON cat.CategoryId = coinCat.CategoryId
WHERE RowNumber>= @pageNumber * 1 AND RowNumber<@pageNumber *@pageSize
ORDER BY RowNumber

The statement must be nested in order to please Dapper (ORM),

Before applying/paging, I would like my results to be sorted by Coins.Rank but not sure where to stick that part so that my query can run properly and paging works happily

Also would this statement be any different between SQL Server vs PostgresSQL as an example?

6
  • How is Coins.Rank derived? Is this a column in the table? Is it unique? Part of the key? Also please see this post because all those * references are probably going to make this pretty abysmal as you increase @pageNumber (especially if you don't have an index that supports your sorting). Dec 17, 2018 at 22:22
  • I am an idiot, instead of ORDER BY c.CoinId i just did ORDER BY c.Rank and that worked
    – Zoinky
    Dec 17, 2018 at 22:24
  • Ok, let us know how performance goes as the table gets bigger and page number goes up... Dec 17, 2018 at 22:29
  • Given the query, will performance be an issue for me if i have less than 5000 records in coins?
    – Zoinky
    Dec 17, 2018 at 22:31
  • 1
    You'll have to let us know. Number of rows is not necessarily an important criteria, but rather the number of pages (which is dictated by a combination of number of rows, width of those rows, fragmentation, suitability of indexes, among other things...). Dec 18, 2018 at 0:14

1 Answer 1

3

instead of ORDER BY c.CoinId i just did ORDER BY c.Rank and that worked

SELECT c.*, ROW_NUMBER() OVER (ORDER BY c.Rank) AS RowNumber FROM coins c

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.