5

How would you implement a query that selects every nth row, with NHibernate QueryOver, HQL or Criteria?

Currently I use the following T-SQL query:

SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY Id) AS [Row]
    FROM [TABLE_NAME]
) x WHERE (x.[Row] % 100) = 0

(Thanks to Marc Gravell)

Community
  • 1
  • 1
kshahar
  • 10,423
  • 9
  • 49
  • 73

2 Answers2

2

Have you considered the solution of using an indexing table in a cross join? What I mean is that you have a table with as many rows as you think you will need with an indexed column of integers going from 1-n in each row. This can be in a master database perhaps with a date column beside it - its amazing how useful this method is. The query would then look like

SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY Id) AS [Row]
    FROM [TABLE_NAME]
) x INNER JOIN [Index_Table] i ON i.Id*100=x.[Row]
Dale M
  • 2,453
  • 1
  • 13
  • 21
0

Same as L2S - there's no easy way to do this without SQL. And the syntax would be DBMS-specific anyway.

Diego Mijelshon
  • 52,548
  • 16
  • 116
  • 154
  • The solution using row_number() is pretty DBMS independent (as all modern DBMS support that nowadays). The only thing that is DBMS dependent is the modulo operator. –  Oct 15 '12 at 16:42