SQL Problem

ChicoMendez

Hi

in messages table my messages have a thread column

However i want to return only one message from each thread .ie in someway modify the sql select saying :
WHERE a.thread is UNIQUE.

Can anyone point me inm the right direction?

SQL is as follows:

SELECT a. * , b.id as category, b.published as published
messages AS a,
categories AS b WHERE a.catid = b.id
AND a.hold = 0 AND b.published = 1
ORDER BY a.time DESC
LIMIT 5


TNX

Kai

Try :

SELECT DISTINCT a.thread,<whatever columns you want from a> , b.id as category, b.published as published
messages AS a,
categories AS b WHERE a.catid = b.id
AND a.hold = 0 AND b.published = 1
ORDER BY a.time DESC
LIMIT 5

The Distinct Keyword returns unique fields. That SQL may not work but it may point you in the right direction.

ChicoMendez

ive tryed this


SELECT DISTINCT a.thread,a.catid,a.id,a.subject,a.name
FROM messages AS a,
categories AS b WHERE a.catid = b.id
AND a.hold = 0 AND b.published = 1
ORDER BY a.time DESC
LIMIT 5

but i get back 5 rows-
the first 3 rows have a thread value of 299

Should my DISTINCT not have made sure that only one comes back for each thread value??

Tnx

DJB

I have just consulted my trusty "programmers guide to sql" that I keep close to my desk!

I'll simplify an example to explain DISTINCT.

Example 1
strSQL = "SELECT DISTINCT ColumnA FROM TblA"

This will return all rows with unique data in ColumnA so no duplicates will show up.

Example 2
strSQL = "SELECT DISTINCT ColumnA, ColumnB FROM TblA"

This will return all rows with a unique combination of data between the two fields. Here's some sample data to explain:

ID, ColumnA, ColumnB
1, asdf, 123
2, fdsa, 123
3, asdf, 123
4, dfsa, 123

The returned rows would be rows 1, 2 and 4. Basically, even though 123 appears in all rows, it is the combination of the asdf and 123 on two different rows that will eliminate one of them. That make sense?

It's late and my brains is mashed so can't work out the way around your problem but that might help you.

Dave

McGintyMcGoo

Stick
GROUP BY a.thread
in there and se what you get.