selecting row with highest value and a join

Question

Ho can i only fetch the rows with the highest cvID value?

current code

SELECT 
  CollectionVersionBlocks.cID,
  CollectionVersionBlocks.cbDisplayOrder,
  CollectionVersionBlocks.cvID,
  btContentLocal.bID,
  btContentLocal.content 
FROM
  CollectionVersionBlocks 
  INNER JOIN btContentLocal 
    ON CollectionVersionBlocks.bID = btContentLocal.bID 
WHERE (CollectionVersionBlocks.cID = 259)
  AND CollectionVersionBlocks.isOriginal = 1 
  AND CollectionVersionBlocks.arHandle = 'main' 
  AND btContentLocal.content != ''

I want to get the row at the bottom (where the cvID value is 10).

This is a test statement for a bigger result set -

I will eventually need a set of results from perset cIDs (CollectionVersionBlocks.cID = 259 OR CollectionVersionBlocks.cID = 260... upto 800)

picture of result

updated screenshots

1) too few results 2) un grouped results

result2

enter image description here

MySQL. Tried max(CollectionVersionBlocks.cvID) but it give me an incorrect join on btContentLocal. It shows bID 349 for some reason — v3nt, Apr 30 '14 at 11:59
@M Khalid Junaid - what is that? Ideally the statement should select the row with cvID = 10. — v3nt, Apr 30 '14 at 12:04
If you need one row with highest value you can do so `ORDER BY CollectionVersionBlocks.cvID DESC LIMIT 1` — M Khalid Junaid, Apr 30 '14 at 12:06
I actually need a set of results from preset cIDs (CollectionVersionBlocks.cID = 259 OR CollectionVersionBlocks.cID = 260... upto 800) — v3nt, Apr 30 '14 at 12:12

score 2 · Answer 1 · answered Apr 30 '14 at 12:28

2

To get the highest row per group (from your question i assume cID as a single group) you can do so by using a self join on the maxima of your desired column by using additional condition in in your third join i.e ON(c.cID=cc.cID AND c.cvID=cc.cvID)

SELECT 
  c.cID,
  c.cbDisplayOrder,
  c.cvID,
  b.bID,
  b.content 
FROM
  CollectionVersionBlocks  c
  INNER JOIN btContentLocal b
    ON (c.bID = b.bID)
  INNER JOIN 
  (SELECT cID, MAX(cvID) cvID FROM CollectionVersionBlocks GROUP BY cID) cc
  ON(c.cID=cc.cID AND c.cvID=cc.cvID)
WHERE (c.cID = 259)
  AND c.isOriginal = 1 
  AND c.arHandle = 'main' 
  AND b.content != ''

and for multiple groups you can just use WHERE c.cID IN(259,....800)

answered Apr 30 '14 at 12:28

M Khalid Junaid

63,861
10
90
118

thanks M Khalid Junaid, that's closer but it seems to exclude some results [see new screenshot]. Its something to do with the 2nd INNER JOIN. – v3nt Apr 30 '14 at 12:55
@danielCrabbe where you have posted screenshot ? and its better if you can provide [**sql fiddle demo**](http://www.sqlfiddle.com/) of your sample dataset – M Khalid Junaid Apr 30 '14 at 12:58
up top. I would do but this data is a mess and has tens of thousands of rows and entries and 150+ tables. – v3nt Apr 30 '14 at 13:00
@danielCrabbe no no you don't need to provide whole data set just a sample dataset the rows you have shown in image will be enough in fiddle, and also these 2 tables not all tables from database – M Khalid Junaid Apr 30 '14 at 13:02
I can't get them out as SQL as it keeps crashing but tables are fiddled now. http://www.sqlfiddle.com/#!2/5a0d6 – v3nt Apr 30 '14 at 13:14
@danielCrabbe without sample dataset i am unable to provide you the exact query without trying with dataset ,but the approach to the highest value per group i have shown already – M Khalid Junaid Apr 30 '14 at 19:57

score 0 · Answer 2 · answered Apr 30 '14 at 11:57

Try below Query:

SELECT CollectionVersionBlocks.cID,CollectionVersionBlocks.cbDisplayOrder,        CollectionVersionBlocks.cvID ,  btContentLocal.bID , btContentLocal.content 
FROM CollectionVersionBlocks
INNER JOIN btContentLocal
ON CollectionVersionBlocks.bID=btContentLocal.bID 
WHERE (CollectionVersionBlocks.cID = 259) 
AND CollectionVersionBlocks.isOriginal=1 AND CollectionVersionBlocks.arHandle ='main'            AND           btContentLocal.content !='' and CollectionVersionBlocks.cID in
(

    SELECT Max(CollectionVersionBlocks.cID)
    FROM CollectionVersionBlocks
    INNER JOIN btContentLocal
    ON CollectionVersionBlocks.bID=btContentLocal.bID 
    WHERE (CollectionVersionBlocks.cID = 259) 
    AND CollectionVersionBlocks.isOriginal=1 AND CollectionVersionBlocks.arHandle   ='main' AND btContentLocal.content !='' )

thanks - tried that but it give me 4 results still. (the high value is from cvID not cID) — v3nt, Apr 30 '14 at 12:02

selecting row with highest value and a join

2 Answers2