Linq to SQL Every Nth Row from Table

LINQ to SQL Every Nth Row From Table

Sometimes, TSQL is the way to go. I would use ExecuteQuery<T> here:

    var data = db.ExecuteQuery<SomeObjectType>(@"
SELECT * FROM 
(SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS [__row]
FROM [YourTable]) x WHERE (x.__row % 25) = 1");

You could also swap out the n:

    var data = db.ExecuteQuery<SomeObjectType>(@"
DECLARE @n int = 2
SELECT * FROM 
(SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS [__row]
FROM [YourTable]) x WHERE (x.__row % @n) = 1", n);

Fetch every nth row with LINQ

You can use the Select overload that includes the item index of enumerations. Something like this should do the trick --

var data = myDataLogEnumeration.
        Select((dt,i) => new { DataLog = dt, Index = i }).
        Where(x => x.Index % nth == 0).
        Select(x => x.DataLog);

If you need to limit the query with a Where or sort with OrderBy, you must do it before the first Select, otherwise the indexes will be all wrong --

var data = myDataLogEnumeration.
        Where(dt => dt.DateTime > dateLimit).
        OrderBy(dt => dt.SomeField).
        Select((dt,i) => new { DataLog = dt, Index = i }).
        Where(x => x.Index % nth == 0).
        Select(x => x.DataLog);

Unfortunately, as juharr commented, this overload is not supported in Entity Framework. One way to deal with this is to do something like this --

var data = entity.DataLogSet.
        Where(dt => dt.DateTime > dateLimit).
        OrderBy(dt => dt.SomeField).
        ToArray().
        Select((dt,i) => new { DataLog = dt, Index = i }).
        Where(x => x.Index % nth == 0).
        Select(x => x.DataLog);

Note the addition of a ToArray(). This isn't ideal though as it will force loading all the data that matches the initial query before selecting only every nth row.

Return row of every n'th record

This is where ROW_NUMBER can help. It requires an order-by clause but this is okay because an order-by is present (and required to guarantee a particular order).

SELECT t.id, t.key
FROM
(
    SELECT id, key, ROW_NUMBER() OVER (ORDER BY key) AS rownum
    FROM datatable
) AS t
WHERE t.rownum % 30 = 0    -- or % 40 etc
ORDER BY t.key

Pair entry of every nth row with entry of every (n+1)th row

;WITH cte AS (
    SELECT *,ROW_NUMBER() OVER (ORDER BY Wins DESC) as RowNum
    FROM
       @Table
)

SELECT *
FROM
    cte c1
    LEFT JOIN cte c2
    ON c1.RowNum + 1 = c2.RowNum
WHERE
    c1.RowNum % 2 <> 0

Generate a ROW_NUMBER to use, seeing you have a third Column replace (SELECT NULL) in the Order by statement with that third column.

Then select all rows that are Odd Row numbers (remainder of RowNum divided by 2 <> 0 ) and self join back to itself with RowNum + 1. If you have an odd number of Rows you might consider using LEFT JOIN so you don't drop off the 1 row that won't have a match.

Selecting every nth row from SQL Server 2008 query result where table does not have row id column

You can't reference an alias defined in the SELECT clause in the WHERE clause, since WHERE is parsed first. One workaround is to use a subquery or CTE:

WITH x AS
(
 SELECT ROW_NUMBER() OVER (ORDER BY H1.CombDateTime ASC) AS RowID,
  ... rest of query
)
SELECT CombDateTime, TYA_Close, ESA_Close --, RowID
FROM x
WHERE RowID % 4 = 0
ORDER BY CombDateTime;

Note also what Martin and Marc have pointed out - SQL Server uses % not the MOD operator you're bringing in from VB or elsewhere.

Select every 'nth row in descending order

seems like you just need an order by dec on the desired column in one of the three queries. I think the second one as order by applies to the select at the same level. ans since you want your rownum ordered desc... seems like that's the place...

SELECT * 
FROM ( SELECT @row := @row +1 AS rownum, [column name] 
       FROM ( SELECT * FROM [table name] )
       ORDER BY [column name] desc
     )

WHERE rownum % 5 = 1

how to get second record in linq to sql

You can try this:

var query=data.Skip(1).Take(1);

Querying nth row in Entity Framework

// DO NOT USE THIS FOR MORE THEN 100 ROWS
var randomRecord = foos.OrderBy( x=> SqlFunctions.Rand() ).FirstOrDefault();

But this method is less efficient then,

// USE THIS FOR MORE THEN 100 ROWS
var random = Math.Random(foos.Count());

var randomRecord = foos.OrderBy( x=> x.id ).Skip( random ).FirstOrDefault();

For database, querying count is much less overhead then actually performing SORT over RANDOM for thousands of records. As RANDOM is certainly not indexed, so it will take very long to sort. So avoid using first method, use 2nd method that is the best.

Getting Nth value with Linq

var nthItem = items.Skip(n).First();

---

Related Topics