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

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.