What is the best way to retrieve distinct / unique values using SPQuery?

sharepoint caml spquery
Alex Angas picture Alex Angas · Feb 18, 2009 · Viewed 47.7k times · Source

I have a list that looks like:

Movie          Year
-----          ----
Fight Club     1999
The Matrix     1999
Pulp Fiction   1994

Using CAML and the SPQuery object I need to get a distinct list of items from the Year column which will populate a drop down control.

Searching around there doesn't appear to be a way of doing this within the CAML query. I'm wondering how people have gone about achieving this?

Answer

user4531 picture user4531 · Feb 18, 2009

Another way to do this is to use DataView.ToTable-Method - its first parameter is the one that makes the list distinct.

            SPList movies = SPContext.Current.Web.Lists["Movies"];
            SPQuery query = new SPQuery();
            query.Query = "<OrderBy><FieldRef Name='Year' /></OrderBy>";

            DataTable tempTbl = movies.GetItems(query).GetDataTable();
            DataView v = new DataView(tempTbl);
            String[] columns = {"Year"};
            DataTable tbl = v.ToTable(true, columns);

You can then proceed using the DataTable tbl.

Related questions

CAML query that includes folders in result set

I'm trying to write a CAML query that executes against a specific SPList, scoped to a specific folder, recursive from that point, and returns all ListItems (which meet a criteria) and Folders. Here's the code for the query which seems …

Read More
How to get all folders in a SPList, then checking permission "Contribute" for current user

I have a sharepoint list like that: List ---------Folder 1 -----------------Item 1 -----------------Item 2 ---------Folder 2 -----------------Item 1 -----------------Item 2 ---------Folder 3 -----------------Item 1 -----------------Item 2 How can I get all Folders in List? After that checking if current user has Contribute permission on Folder 1, Folder 2, Folder 3?

Read More
CAML query with nested ANDs and ORs for multiple fields

I am working on proof-of-concept code to dynamically generate CAML based on keywords provided to a highly-specific search web service that I am writing. I am not using the SharePoint-provided search web service for this proof. I have done so …

Read More