So, we have a customer who has a library of Newsletters.
These newsletters belong to different regions, and they have a date on which they should be ‘published’ – or at least, a date at which they’re the most recent item. So, we have two columns to capture that – News Region and Publish Date.
The customer wanted a web part on a page that would show the most recently ‘published’ item for each of those regions. Something like:
I did this with the Content Query Web Part (CQWP) – but how?
Well, first I set my CQWP to query the list of newsletters. They have a specific content type that I queried for. I filtered items with a Publish Date in the future out – although not a secure way of preventing users seeing these items, at least it doesn’t make the obvious.
Next, I set the CQWP to group by News Region, and Sort by descenting Publish Date. So far, so standard – but it would also return multiple items for each region, and display their title, not their region.
This called for a special Itemstyle, which I added using the technique mentioned before for putting ItemStyles into a different file. This made sense; we’re not going to use this style extensively, so it’s best to keep it separate.
Next, I modified my CommonViewFields to bring in the News Region column. I’ve mentioned this before.
Then I wrote my XSL:
Take a look at it – it’s cool.
Looking at the code, you can see I read the News Region that we’re looking at into a variable. I then count up the number of preceding nodes which also have the same value for their News Region.
The first node with a particular News Region value will always be the one with the latest Publish Date that isn’t in the future – therefore, the most recent one for that region. The count statement won’t count nodes for other regions, either. So, if it’s the first, we display it – using the News Region text, rather than the title. Simple!
(But a bugger of an XSL query to figure out)
Andy Burns' Blog 
hmmmmm good post.
thank you.
GREAT post! You just saved me hours of time.
I have another solution and its very simple:
Ex: if you have 2 columns (category) and (Year), if you want to make a group by Category, and display the first row in each group,
1- in SharePoint designer insert new data view,
2- insert year column.
3- go to sort and group -> group by category and make sure you select check (show group header).
4- sort by year.
5- now in SharePoint designer, in design view, you will see the group header and under each group you see items for this group, only drag the item and drop to the header TD.
Thanks for this idea – I think I am very close, but the last bit of code does not work.
As you described, I created a template that is linked to my CQWP – for convenience sake, I edited the general stylesheet. I still can then migrate to a individual linked file later.
After creating the rows how I wanted them (the first template) and viewing them nicely, I went ahead and added a template with your filtering mechanism. Now however the cqwp seems to be broken. I think something must be broken in the second template. If I reroll without the filtering template, then everything works nicely.
The goal is to have a listing where for each “register designation” the latest “Total assets held” is displayed. Latest could be defined either by date or by “trade-id” – an increasing number)
Thanks for your help, I hope you find the time to have a look: the snipplet of the templates is https://www.box.com/s/f2m0148xgqg1smnpz39b.
You have 2 round opening brackets on line 58 that need to be square ones. I don’t know if that’s the only issue, but certainly, those brackets don’t match!
thanks – that certainly was not correct but there still seems to be the same problem. The CQWP is still not displayed. Again, If I take your template out, the webpart works nicely again.
Hi Andy,
I have one question I have configured CQWP for GroupBy it works great, but how do I display first group of data only? another question is how do I display excluding first group of data?
Thanks for you help
Chinna
Using xslt will work, however, with thousands of data, you’d still be GETTING all data that xslt will test if indeed exeed top n per group. Is there a way to get only my top n before xslt proceed with it work?