I would like to retrieve random item from SharePoint list.

I have these approaches:

  1. Get random index and use list.items[randomIndex]. But I think that I retrieves all items from DB in this case.
  2. Create query to retrieve all ids from list, then select random id and use list.GetItemById(id).
  3. Get last record id, select random value from 0 to this id, try to get item by selected value. If an item does not exist, repeat steps.

How can I do this in the best way? Your solutions?

  • could you give us more information on the items that you will be pulling? For example, what is the likelihood of the items containing all indexes to the max? Will items be deleted and added frequently? The best solution will depend highly on the reliability of consecutive indexes
    – Zork
    Mar 21, 2012 at 14:15
  • @Zork, it should be a picture library, users can add and delete pictures, but I don't think that it will be frequently.
    – Alexander
    Mar 21, 2012 at 14:20

4 Answers 4


if we can add a new column then we can resolve with event receiver:

  1. Add a column "Index"
  2. Once item is added we assign to "Index" a value - value of max "Index" + 1
  3. Once item is deleted we find item with max "Index" and set it to index of deleted item
  4. If we delete item with max "Index" we do nothing

Thus there is always a ordered list of consecutive indexes. You can get an element with maximum "Index" and get a random from 0 to it's value. And you always have an element with such "Index".


#3 is the best if it's a list with very few deletes

#1 is probably the best if it's a small list with lots of deletes

#2 is useless as it's #1 with one extra read

If it's a large list with a lot of deletes then and this is a must then I'd:

  • Add a help id which is set to be a unique number 1 to ItemCount
  • When a new record is added the helpId is set to ItemCount+1
  • A timer job is created to "Renumber" helpid
  • To get a random record pick a random number 1..ItemCount. Run a query to get the first record with helpid > the number
  • #2 depends upon the query and the list that's returned which can be shorter than #1. The query can also take care of large lists (as mentioned before). Mar 21, 2012 at 22:46

I thought a bit more about this and determined the best possible option to be option 2. Guaranteeing two and only two queries would be the best choice.

Due to SharePoint being a SQL based product and the low amount of overhead for even large result sets, especially reduced to just IDs and no other information makes the 2nd choice ideal. Once you query for all ids from the list, instead of selecting a random id, select a random index from the returned result set. That would guarantee a successful result. Then grab the id from the chosen index and pull the Item.

The ID query is efficient as is only pulling the data from the file needed. Furthermore ensuring that this will always be the case is always beneficial.


Approach #2 is the best of the lot but there are some points to consider - especially for large picture library that has more than 2000 items (constraints due to SharePoint 2010 List items throttling). Approaches #1 & 3 may hit on a non-existent list item. Some PowerShell code to illustrate the approach:

#Create fooIndexList by iterating through all items in the picturelib.
#fooIndexList is created at run when picture items are added/deleted.
#For larger picture lib that has more than 2000 items see link below*
$fooIndexList = 13,45,556,24,46,345,73, 84, 921, 692, 44,723,733,92,133 #array of unique item ids from picturelib
$fooChosenIndex = $fooIndexList | Get-Random   #Get-Random has unique seed value using timestamp as default. Set seed value option for debugging, etc.
$fooChosenIndex  #this is the 'random' selected picture item..

*For large lists - Iterating Large SharePoint Lists with PowerShell

  • You suggest to use approch #2 and use this links to retrieve IDs from large list, am I right?
    – Alexander
    Mar 21, 2012 at 17:27
  • Yes but the main thing is to create the array'fooIndexList' and that can be done using a query or just iterating through the picture library and getting the picture item's id. The link illustrates how to go through larger lists - instead of 'Write-Host $item.Title' in the ForEach loop, the item's unique id is obtained and inserted in the array fooIndexList. Mar 21, 2012 at 17:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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