SQL Select/Join, LIMIT result to 1st entry per element in query list

Question

I have a query like so

Select * from tableA A 
LEFT JOIN
mydb.tableB B
ON A.pk = B.tableA_pk 
LEFT JOIN 
mydb.tableC C    
ON B.tableC_pk = C.pk
WHERE C.PK  IN ('325','305', '322')
ORDER BY A.pk desc;

This will return a result like so with potentially multiple rows for an entry where applicable

4, xxx, yyy, 325, 325, zzz <<<< most recent for this entry ( 'unique' key is tableC.pk, 325)
3, aaa, bbb, 325, 325, eee <<<< next most recent 
3, ccc, ddd, 322, 322, fff
2, eee, fff, 305, 305, rrr 
2, ggg, hhhh,322, 322, ttt
1, iii, jjj, 325, 325, uuu <<< oldest

Ideally i want the result to look like below, that is for each matching entry in the LIST ..IN ('325', '322') it should only return the most recent entry like below

4, xxx, yyy, 325, 325, zzz <<<< most recent
3, ccc, ddd, 322, 322, fff
2, eee, fff, 305, 305, rrr

To explain further..

Select * from tableA A 
LEFT JOIN
mydb.tableB B
ON A.pk = B.tableA_pk 
LEFT JOIN 
mydb.tableC C    
ON B.tableC_pk = C.pk
WHERE C.PK  IN ('325')
ORDER BY A.pk desc;

I would get this

4, xxx, yyy, 325, 325, zzz <<<< most recent for this entry 
3, aaa, bbb, 325, 325, eee <<<< next most recent 
1, iii, jjj, 325, 325, uuu <<< oldest

But I need this:

4, xxx, yyy, 325, 325, zzz <<<< most recent for this entry ( 'unique' key is tableC.pk)

I have tried LIMIT 1 (I'm using MySQL) like below but this limits to 1 for the total request not 1 per searched entry in my query list.

Select * from tableA A 
LEFT JOIN
mydb.tableB B
ON A.pk = B.tableA_pk 
LEFT JOIN 
mydb.tableC C    
ON B.tableC_pk = C.pk
WHERE C.pk  IN ('325','305', '322')
ORDER BY A.pk desc
LIMIT 1;

Well you can achieve it using Row_Number() function with Partition By Clause. — Krishnraj Rana, Sep 05 '14 at 22:15
If you provide adequate information on your table structure and some sample data I'll tell you how to do this. — jpw, Sep 05 '14 at 22:15
possible duplicate of [Retrieving the last record in each group](http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group) — Bulat, Sep 05 '14 at 22:16
Can you create the schema in [SQL fiddle](http://sqlfiddle.com/) ? So that we can help you betterly. — Krishnraj Rana, Sep 05 '14 at 22:16
also http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column/25694562#25694562 — Bulat, Sep 05 '14 at 22:17
a correlated subquery is probably the *best* way to solve this! — Strawberry, Sep 05 '14 at 22:57

score 0 · Answer 1 · answered Sep 05 '14 at 22:30

You can use variable by iterating it with 1 like so.

Select A.*, @i:=@i+1 AS rownum
from tableA A 
LEFT JOIN
mydb.tableB B
ON A.pk = B.tableA_pk 
LEFT JOIN 
mydb.tableC C    
ON B.tableC_pk = C.pk
WHERE C.PK IN ('325') and (SELECT @i:= 0) r
ORDER BY A.pk desc;

Note: I have not tested above query.

score 0 · Accepted Answer · answered Sep 08 '14 at 20:00

After reading suggestions and other answers I achieved it like so..many thanks for all help....

SELECT
DISTINCT C.PK
,B.PK
,B.tableC_pk
,B.tableA_pk
,A.pk

FROM

tableC C
INNER JOIN
tableB B
ON C.PK = B.tableC_pk
INNER JOIN
tableA A
ON A.PK = B.tableA_pk

INNER JOIN ( SELECT tableC_pk,MAX(PK) AS PK FROM tableB
WHERE tableC_pk IN ('325','322','111')
GROUP BY 1) D
ON C.PK = B.tableC_pk
AND B.PK = D.PK

score -1 · Answer 3 · edited May 23 '17 at 12:17

-1

This question is asked several times a day. Here you will find an answer:

SQL Select only rows with Max Value on a Column

edited May 23 '17 at 12:17

Community

1
1

answered Sep 05 '14 at 22:23

Bulat

6,869
1
29
52

1

I flagged it as duplicate. – Bulat Sep 05 '14 at 22:25
2

Oh ok. Then why post an answer? My understanding is that questions that should be closed aren't supposed to be answered. – jpw Sep 05 '14 at 22:28
3

I guess. I have flagged several questions like this and none of them was closed, so I just answer with the link, if it gets accepted it will only confirm that its a dupe. Or author can remove the question, that will be even better. – Bulat Sep 05 '14 at 22:34

SQL Select/Join, LIMIT result to 1st entry per element in query list

3 Answers3