I'm looking for a new SS 3.x solution to an old problem. I have a list of news items stored in a DB which cover several years. I want to create a link for each year represented by the news items (e.g., 2006, 2007... 2013). If I get all the news items, I'll have multiple items per year, when all I really want is a DISTINCT year.
In SQL terms, I would like to SELECT DISTINCT YEAR(Date) FROM NewsItems ... and turn the result into a data list.
In my template, I'd would like to do
<% loop Years %>
<% end_loop %>
But if I just build a simple DataList, I'll get a link for each article, not each year in which there is an article.
Is there a way to get the whole table of NewsItems into a DataList and then efficiently perform an operation on the DL to just get the distinct years? I can imagine doing it with a loop, but it seems like a kludge.
SQLQuery is another option but it's throwing up all sort of deprecation notices...
Please help. Thanks.
P.S. I could also just build the HTML by hand and stick into a LiteralField or return it as a string but that's not using the template system.