[Resolved] Getting row with the gratest column-value

tdomega

Hi,

I have a problem with my mysqlquery and thought you may able to help me 🙂

I have a table1 with id,subject,txt,startid,time
all email-posts that belong together have the same startid.
the initial post.id=startid

now my problem is how can i get the subject of the last post if i only have the id from one in the middle.

this is my current query as i need also the first entry:

$query="select t1.subject, first.subject as fsubject from table1 as t1 left join  table1 as first on first.ID=t1.startid where t1.id=".$id;

now there need to be an additional left join, but i cant figure it out, may something have to do with gratest or max, but the mysql-manual is a little vague on these commands...

TTT

SELECT MAX(fieldName) AS fn FROM table WHERE 1=1

leatherback

Hi,

I am a bit confused as to WHY you are using a join.

Isn't this what you want:

'select * from table1 where startid=$id order by id desc limit 1'

tdomega

Originally posted by TTT
SELECT MAX(fieldName) AS fn FROM table WHERE 1=1

If I try this I get a "Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause" MySql-Error

Originally posted by leatherback
Hi,

I am a bit confused as to WHY you are using a join.

Isn't this what you want:

'select * from table1 where startid=$id order by id desc limit 1'

J.

i dont have the startid, only the id of a post that might be start/middle/end or all at once so i need a query that fits all cases.
there are other entries i need to retrieve from different tables that makes the join necessary but i left them out for simplicity

if you think it might be neccessary i will post the whole query, but it is kinda big...

tdomega

ok, i got it working now with max() and adding group by id

it works, but i dont know how...what excactly is group by, i only see that i loose some results (which i dont need, but could be coincidence...)
if someone could explain or point me into the right direction please? 😃

[edit]
oh, and while were at it, what would be faster a really big query referencing 5 tables (through joins) or about 6 querys and some if's? just in general...
i only thought it maybe worthless putting it all together in one query...:eek:
[/edit]