how to pull only unique items from database?

trying to display 3 latest content items by DIFFERENT authors

[gilahacker]

I have a table like this: date, author, title, content

now I want to grab the 3 latest items from this table from 3 DIFFERENT authors.

I want this:
-article by jim
-article by sally
-article by john

Not this:
-article by jim
-article by jim
-article by sally

even if the two latest articles happen to be by jim, lets only grab the first one and move on.

So basically I need (in pseudo-sql/plaintext):
select title from contenttable where author=unique order by date limit 3

I've got everything code-wise except the "author=unique" part, how do I do that?

-Jason
(cries for help!)

[jatar_k]

what about

select distinct author, title from contenttable order by date limit 3

[tomda]

Try also a "group by author", Jatar_k solution may be better though.

[gilahacker]

ok, this seems to work:

SELECT title, date, content from contenttable GROUP BY author ORDER by date DESC LIMIT 3

my real sql query is of course much longer, limiting it to articles in certain categories, making sure they're published, etc. but the basics above seem to have fixed my problem.

the DISTINCT author made sense to me but it didn't seem to do anything.

Thanks for your help guys!

-JL

[gilahacker]

ok, weird problem.

I've got this:
SELECT title, create_date, author FROM contenttable GROUP BY author ORDER BY create_date DESC LIMIT 3

and it's returning 3 items, from three different authors like I wanted. But it's not returning the latest item from each author but rather appears to be grabbing the first one it comes across from each author.

I tried switching the ORDER BY and GROUP BY sections but only got mysql errors telling me to "check your syntax" blah blah blah. I thought that if it sorted first, then grouped I'd be okay. But it seems mysql doesn't want to do that.

Anyone have any suggestions or ideas?

Thanks in advance!

-Jason

[gilahacker]

Anyone? Anyone? Bueler? Bueler?

AAAHHH!

Can't figure this thing out...

It's not grabbing the latest articles by those authors...

basically it's going through and finding the last 5 (unique) authors that posted articles. Then it's grabbing the first article that author wrote, not the most recent one...

[pulls hair out...]

Help!

Thanks again,
-Jason

[Lord Majestic]

Try this:

select author,max(Date) as Date from table group by author order by 2 DESC limit 3

This will give you most recent authors and dates of their articles, now you just need to run query which would join with the data above on author and date with table in question giving you what you want. If you had unique row number (articleIDs) it would have been even easier:

select * from table where ArticleID in
(select max(ArticleID) as ArticleID from table group by author order by ArticleID DESC limit 3)

[gilahacker]

@lord majestic

Try this:
select author,max(Date) as Date from table group by author order by 2 DESC limit 3

This will give you most recent authors and dates of their articles, now you just need to run query which would join with the data above on author and date with table in question giving you what you want. If you had unique row number (articleIDs) it would have been even easier:

select * from table where ArticleID in
(select max(ArticleID) as ArticleID from table group by author order by ArticleID DESC limit 3)

I DO have unique article id's... I'm going to try this out first thing tomorrow.

THANKS!

-Jason

[gilahacker]

@lord majestic

Okay I've got this:

$query = "SELECT * FROM database.table WHERE id IN (SELECT MAX(id) as id from database.table GROUP BY author ORDER BY id DESC limit 5)";

but I get this:

Notice: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT MAX(id) as id from database.table GROUP BY aut in /home/userid/public_html/includes/database.php on line 184

note: I changed the actual database and table names to make it shorter and easier to read but they are correct in my code.

What did I miss?

Thanks,
-Jason

[jatar_k]

what version of mysql do you have? sub selects aren't allowed below 4.x or some such version

[Lord Majestic]

Here is what I've done:

Test table:

create table articles (articleid int,date datetime,author varchar(64),title varchar(64))

insert into articles values (1,'2005-05-05','jim','article 1');
insert into articles values (2,'2005-05-05','sally','article 2');
insert into articles values (3,'2005-05-05','john','article 3');
insert into articles values (4,'2005-05-05','jim','article 4');
insert into articles values (5,'2005-05-05','john','article 5');

--

Here is SQL to get latest 3 articles by unique authors:

select * from articles where articleid in (select max(articleid) from articles group by author)

If your version of mysql does not support subqueries then run it in 2 queries:

1) select max(articleid) from articles group by author limit 3

to get 3 articleid's, and then use them in second query:

2) select * from articles where articleid in (articleID1,articleID2,articleID3)

[gilahacker]

I'm with LunarPages and they're running MySQL "4.0.23-standard"

This version doesn't seem to support nesting of SELECT statements.

I ran select max(articleid) from articles group by author limit 3 and got the first 3 articles (by unique authors) from the table, not the 3 most recent articles.

something's not right with the MAX() I haven't actually been able to make it do anything... Been fighting with this for a few hours now.

Basically I can take out the MAX() and the GROUP BY and get the same exact results from the query.

Perhaps there's a way to get a list of the 5 most recent authors to post. Then to run another query to find the most recent articles by those authors?

Thanks again for all your help.

-Jason

[Lord Majestic]

Try this then:

select author,max(articleid) as articleid from articles group by author order by 2 DESC limit 3

This should give last 3 authors and their most recent article ids

[gilahacker]

my three most recent authors to post are 86, 107, and 76 (their names are in a seperate table)

select author,max(articleid) as articleid from articles group by author order by 2 DESC limit 3
gives me 70, 79, and 81

Now due to the nature of this content management system, the articleid won't count up forever, so we should probably use MAX(create_date) instead. However, at this point in time either articleid or create_date give the same results. Unfortunately, they're both coming out wrong.

I'm starting to think my version of mysql is a bit screwy...

Now, SELECT author FROM table order by create_date DESC gives me 86, 107, 107, 107, 76, 76, 76, 76, 76, ... (158 rows total). #86 submitted one article, #107 submitted three, and #76 submitted a ton.

I could then take this and strip out the redundant entries with php. (at least I think I can do that).

Then I could run a few more queries like this: (probably in a for loop)
SELECT create_date, title, articleid FROM mos_content WHERE author='$author[$i]' order by created DESC LIMIT 1

which would grab the latest article from that particular author.

It's a Bass-Ackwards way of doing it but I can't figure out any other way. In theory it should work right?

Thanks,
-Jason

[jatar_k]

yes, it should, multiple queries isn't a big deal usually