DESC malfunction?

[BEFOR]

I have DESC with an INNER JOIN query. The DESC rule is followed. But when I place the limit 1 it errors.

• last lines of query are:

ORDER BY mpnT.MpltID DESC, md.sel_order

//mpnT.MpltID is the primary id of a history table. I need only the last row, others are out of date.

// md.sel_order comes from a many to many table (composite index. it was added to maintain the order, numbers 1-6 inserted)

whenever I try to add the limit 1 to mpnT.MpltID DESC (to eliminate all rows being returned) it gives: "server version for the right syntax to use near 'LIMIT 0, 6' at line 1 " Yet I never mentioned the 6.

My server does not support MAX.

How can I make the 'DESC LIMIT 1 'work?

[Lars_Berg]

Show the complete query.

[BEFOR]

-would very much appreciate your help.
(last line simply reversed)

SELECT Mm_ID, Mplan, Mcdm_ID, md.sel_order, MCat_ID, mctg.MCatID, mctg.MCatName, mpnT.MpltID
FROM MplanT mpnT, Mcateg mctg INNER JOIN MwcatD md ON mpnT.Mm_ID = md.Mcdm_ID INNER JOIN
Mcateg mg ON mg.MCatID = md.Mcdm_ID AND md.Mcdm_ID = colname AND md.MCat_ID=mctg.MCatID
ORDER BY md.sel_order ASC, mpnT.MpltID DESC

[Sxooter]

that's missing the limit part, but...

try limit 0,1 at the end and see if that works.

[BEFOR]

ORDER BY md.sel_order ASC, mpnT.MpltID DESC LIMIT 0,1

gives:

Check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 6' at line 1

How can this be?

[Sxooter]

So, what version are you using?

[Sxooter]

AND md.Mcdm_ID = colname AND md.MCat_ID=mctg.MCatID

Just wondering, what happens if you clarify the table thta goes with the column colname up there?

Just wondering.

[BEFOR]

version is 4.0.22 but I do use DESC LIMT 1,1 on other pages of the site, no problem.

If by clarify this you mean:

AND md.Mcdm_ID = colname AND md.MCat_ID=mctg.MCatID

becomes:

AND MwcatD.Mcdm_ID = colname AND md.MCat_ID=mctg.MCatID

get error "unknown table" in on clause

If that's what you mean

tried to just: ORDER BY mpnT.MpltID DESC LIMIT 1

• getting same error message -

Is there a way to list the ORDER BY's seperately or else combine the joins. Isn't the problem the DESC occurs after the join is accomplished? Can I list the DESC LIMIT 1,1 higher up within the join without a subcategory?/MAX

[Sxooter]

no I mean columnname.

It should be sometablealias.columnname

[BEFOR]

md.Mcdm_ID = mpnT.colname AND md.MCat_ID=mctg.MCatID

--gives syntax error

md.Mcdm_ID = mpnT.Mm_ID=colname AND md.MCat_ID=mctg.MCatID

--gives no error, and no results

okay, starting with this:
ORDER BY md.sel_order ASC, mpnT.MpltID DESC

is there a way to write the ORDER BY so for every md.sel_order
1-thru-6 do mpnT.MpltID DESC LIMIT 1

Given the error messages, "'LIMIT 0, 6' at line 1 " Which is self-assigning, this is somehow confusing the two. col's

Please look at the original statement. Is it alright to hav two seperate abbr. for Mcateg - mctg - mg ?

[Sxooter]

OK, that colname is bugging me. I'm not sure if it's some freaky shorthand for a column name in mysql or something.

I'm guessing that MySQL is just turning it into a string constant without quotes. Try changing it to xyzzzzz or something and see if you still get no error AND no output.

Yes, it is quite ok to have a table with two aliases. This allows you to basically join two (or more) sets of data from the same table together.

[BEFOR]

md.Mcdm_ID = mpnT.Mm_ID=zzzz AND md.MCat_ID=mctg.MCatID
ORDER BY md.sel_order ASC, mpnT.MpltID DESC

again no error, no output

Using the LIMIT 1 makes it error.

[Sxooter]

Yeah, I think that colname is supposed to be a string constant or something else to match on. Try removing the whole part AND xxx.yyy=colname and see what you get.

[BEFOR]

I'm matching the colname on a session variable

[Sxooter]

hum. Then I'm not sure. Is that a mysql thing, the session variable? Or do you mean a PHP variable?

[BEFOR]

$HTTP_SESSION_VARS['id_name']

[Sxooter]

So, does the query really look like:

AND xxx.yyy=$somvariable

and turn into

AND xxx.yyy=somevalue

[BEFOR]

looks like it does break down to: =%s
//md.Mcdm_ID =zzzz was in the statement, here converted to %s

$query_Dcateg = sprintf(" SELECT Mm_ID, Mplan, Mcdm_ID, md.sel_order, MCat_ID, mctg.MCatID, mctg.MCatName, mpnT.MpltID FROM MplanT mpnT, Mcateg mctg INNER JOIN MwcatD md ON mpnT.Mm_ID = md.Mcdm_ID INNER JOIN Mcateg mg ON mg.MCatID = md.Mcdm_ID AND md.Mcdm_ID =%s AND md.MCat_ID=mctg.MCatID ORDER BY md.sel_order ASC, mpnT.MpltID ", $zzzz_Dcateg);
$query_limit_Dcateg = sprintf("%s LIMIT %d, %d", $query_Dcateg, $startRow_Dcateg, $maxRows_Dcateg);
$Dcateg = mysql_query($query_limit_Dcateg, $bm) or die(mysql_error());
$row_Dcateg = mysql_fetch_assoc($Dcateg);

[Sxooter]

Print out $query_limit_Dcateg right before the mysql_query and show us what THAT is...

[BEFOR]

print $query_limit_Dcateg;

-nothing