I have a subquery where I am retrieving Shipment information from the DB. I want to LIMIT the result to '1' but I first need the results in 'ID' order. When I use this subquery I don't get the FIRST row because the data is not in ID order: (SELECT TextValue FROM tblQuoteItems WHERE (Type = 6 AND (tblQuoteItems.QuoteID = tblQuotes.ID)) LIMIT 1) AS Shipment
When I use this subquery I only get the 'Shipment' data for the first row of the whole query and NULL for the other rows.: (SELECT TextValue FROM tblQuoteItems WHERE (Type = 6 AND (tblQuoteItems.QuoteID = tblQuotes.ID)) ORDER BY tblQuoteItems.ID LIMIT 1) AS Shipment For this subquery I want to first put in ID order then LIMIT the results to 1. How do I do this? If you need the whole query here it is. I cleaned it up a little: SELECT products.lnglaborhrs AS LaborHRS, Products.Category, (SELECT TextValue FROM tblQuoteItems WHERE (Type = 6 AND (tblQuoteItems.QuoteID = tblQuotes.ID)) LIMIT 1) AS Shipment FROM (((tblQuotes INNER JOIN tblQuoteItems ON tblQuotes.ID = tblQuoteItems.QuoteID) LEFT JOIN Products ON tblQuoteItems.ModelNo = Products.ModelNo) INNER JOIN tblOppLog ON tblQuotes.ID = tblOppLog.QuoteID) WHERE ((tblQuoteItems.Type=0) AND tblQuotes.id IN (SELECT DISTINCT max(id) FROM tblQuotes GROUP BY concat(Date_Format(Date,'%y'), '-' , QuoteNumber))) ORDER BY Date_Format(tblquotes.date,"%y"), tblQuotes.QuoteNumber,if(tblQuotes.Revision= "Initial", " ", tblQuotes.Revision);
