I have a table like this
Table A: Id Count 1 4 1 16 1 8 2 10 2 15 3 18 etc Table B: 1 sample1.file 2 sample2.file 3 sample3.file TABLE C: Count fileNumber 16 1234 4 2345 15 3456 18 4567
and so on...
What I want is this
1 sample1.file 1234 2 sample2.file 3456 3 sample3.file 4567
To get the max value from table A I used
Select MAX (Count) from A where Id='1'
This works well but my problem is when combining data with another table.
When I join Table B and Table A, I need to get the MAX for all Ids and in my query I dont know what Id is.
This is my query
SELECT B.*,C.* JOIN A on A.Id = B.ID JOIN C on A.id = B.ID WHERE (SELECT MAX(COUNT) FROM A WHERE Id = <what goes here????>)
To summarise, what I want is Values from Table B, FileNumber from Table c (where the count is Max for ID from table A).
UPDATE: COrrecting table C above. Looks like I need Table A.