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

Question

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 1

There is no DISTINCT in CAML to populate your dropdown try using something like:

foreach (SPListItem listItem in listItems)
    {
        if ( null == ddlYear.Items.FindByText(listItem["Year"].ToString()) )
       {
                   ListItem ThisItem = new ListItem();
                   ThisItem.Text = listItem["Year"].ToString();
                   ThisItem.Value = listItem["Year"].ToString();
                   ddlYear.Items.Add(ThisItem);
        }
   }

Assumes your dropdown is called ddlYear.

Answer 2

Can you switch from SPQuery to SPSiteDataQuery? You should be able to, without any problems.

After that, you can use standard ado.net behaviour:

SPSiteDataQuery query = new SPSiteDataQuery();
/// ... populate your query here. Make sure you add Year to the ViewFields.

DataTable table = SPContext.Current.Web.GetSiteData(query);

//create a new dataview for our table
DataView view = new DataView(table);

//and finally create a new datatable with unique values on the columns specified

DataTable tableUnique = view.ToTable(true, "Year");

Answer 3

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.