SELECT only ONE row with multiple results

[bsamson]

Here's my database:

Table: sapImport
id    invoiceNo    soldOn
1     123456       11/18/2013
2     156541       11/15/2013

Table: conStatus
id    record     statusCode
1     2          999
2     2          000
3     1          213

I have a query that looks like this:

SELECT *
FROM sapImport
JOIN conStatus
ON sapImport.id = record
WHERE store = '2'

So, the row record matches the ID in sapImport. I only want to display the latest record the corresponds to the row with the highest id. I've been trying to accomplish this for a few hours, but still can't figure it out. Any help is greatly appreciated!

 

[MDCode]

ORDER BY id ASC(or DESC they always confuse me) LIMIT 1

 

Edit: I believe it is DESC...idk you can try both

Edited November 18, 2013 by SocialCloud

[bsamson]

Appreciate that. However, I don't want just one total row return. I need one row with the highest ID, then move on to the next match. Does that make sense?

[MDCode]

So you would take out the LIMIT 1?

[Barand]

Where is "store" held? I don't see that column in your data

[Barand]

try

SELECT si.invoiceNo, si.soldOn, cs.statusCode
FROM sapImport si
INNER JOIN conStatus cs ON si.id = cs.record
INNER JOIN
    (
    SELECT record, MAX(id) as id
    FROM conStatus
    GROUP BY record
    ) mx ON cs.record = mx.record AND cs.id = mx.id

[bsamson]

@Barand - You are a genius my friend! Thank you SSOO much!!!!

 

@SocialCloud - Thanks for your help as well.