How do you select distinct records but ordered by date. I am building this website (http://www.neoblack.com), as a hobby, and it's getting more and more popular but would like to return only 1 post per user but order the
list by last post date. If you look at the blog on the right side of the page, you'll notice that the last person who posted, posted about 10 blogs and therefore showing up for all 10 records displayed. What I would like to do is display the last posters (distinct)
and the last post they made.
Any tips would be welcome. The closest I could do, and which did not work was to create a repeater, and then for each item in the repeater I performed a "select top 1" statement ordered by date desc.
But this did not work because I got an error saying "order by items must appear in the select statement if select distinct is specified".
Here is the code:
SELECT DISTINCT Username
FROM site_Blogs
ORDER BY DatePosted DESC
OK, I'm now using this select statement and then binging to a listview:
SELECT Username, MAX(DatePosted) AS DatePosted
FROM site_Blogs
GROUP BY Username
ORDER BY DatePosted DESC
Then for each row in the listview, I perform a "select top 1 blogid, username, dateposted, blog, category from blogs order by dateposted desc" and populate that row with the result.
And this works, but was wondering, for the sake of performance down the road (right now it's not that important since the site has only about 55,000 members and not everyone posts a blog), but if it would be possible to do a single select statement.
SELECT blogid,
username,
dateposted,
blog,
category
FROM (SELECT blogid,
username,
dateposted,
blog,
category,
Row_number() OVER(partition BY Username ORDER BY dateposted DESC) AS rn
FROM blogs) t
WHERE rn = 1
ORDER BY username
jaghadiuno
Member
19 Points
43 Posts
Quick question; distinct records, multiple columns
Apr 26, 2012 05:28 PM|LINK
Hello, I'm hoping someone can help with this.
How do you select distinct records but ordered by date. I am building this website (http://www.neoblack.com), as a hobby, and it's getting more and more popular but would like to return only 1 post per user but order the list by last post date. If you look at the blog on the right side of the page, you'll notice that the last person who posted, posted about 10 blogs and therefore showing up for all 10 records displayed. What I would like to do is display the last posters (distinct) and the last post they made.
Any tips would be welcome. The closest I could do, and which did not work was to create a repeater, and then for each item in the repeater I performed a "select top 1" statement ordered by date desc.
But this did not work because I got an error saying "order by items must appear in the select statement if select distinct is specified".
Here is the code:
SELECT DISTINCT Username
FROM site_Blogs
ORDER BY DatePosted DESC
tusharrs
Contributor
3230 Points
668 Posts
Re: Quick question; distinct records, multiple columns
Apr 26, 2012 05:31 PM|LINK
select bloguser,max(blogdate) from blogs group by bloguser
( Mark as Answer if it helps you out )
View my Blog
gimimex
Participant
1052 Points
157 Posts
Re: Quick question; distinct records, multiple columns
Apr 26, 2012 05:42 PM|LINK
You can complement by adding the clause Order By:
Hope this helps.
jaghadiuno
Member
19 Points
43 Posts
Re: Quick question; distinct records, multiple columns
Apr 26, 2012 05:49 PM|LINK
Wow! Genius.
It worked. Unbelievable! Thanks guys!
jaghadiuno
Member
19 Points
43 Posts
Re: Quick question; distinct records, multiple columns
Apr 26, 2012 06:46 PM|LINK
OK, I'm now using this select statement and then binging to a listview:
SELECT Username, MAX(DatePosted) AS DatePosted
FROM site_Blogs
GROUP BY Username
ORDER BY DatePosted DESC
Then for each row in the listview, I perform a "select top 1 blogid, username, dateposted, blog, category from blogs order by dateposted desc" and populate that row with the result.
And this works, but was wondering, for the sake of performance down the road (right now it's not that important since the site has only about 55,000 members and not everyone posts a blog), but if it would be possible to do a single select statement.
Thank you.
limno
All-Star
117314 Points
7997 Posts
Moderator
MVP
Re: Quick question; distinct records, multiple columns
Apr 26, 2012 07:23 PM|LINK
SELECT blogid, username, dateposted, blog, category FROM (SELECT blogid, username, dateposted, blog, category, Row_number() OVER(partition BY Username ORDER BY dateposted DESC) AS rn FROM blogs) t WHERE rn = 1 ORDER BY usernameFormat your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
jaghadiuno
Member
19 Points
43 Posts
Re: Quick question; distinct records, multiple columns
Apr 26, 2012 08:46 PM|LINK
Wow, speechless. Been trying to list those records this way for over a year now without success.
Not sure how or why this works yet, but it works perfectly.
Thank you.