0

I have two table

The table structure goes something like this

Table A

╔══════╦════════╗
║ P_ID ║ P_NAME ║
╠══════╬════════╣
║    1 ║ name1  ║
║    2 ║ name2  ║
║    3 ║ name3  ║
║    4 ║ name5  ║
╚══════╩════════╝

Table B

╔═════╦════════╦════════╦═══════════╦═══════╗
║ ID  ║  P_ID  ║  C_ID  ║  C_PRICE  ║  TIME ║
╠═════╬════════╬════════╬═══════════╬═══════╣
║  1  ║      1 ║      3 ║        11 ║ 11111 ║
║  2  ║      2 ║      4 ║        22 ║ 22222 ║
║  3  ║      3 ║      5 ║        33 ║ 33333 ║
║  4  ║      3 ║      6 ║        44 ║ 44444 ║
║  5  ║      3 ║      6 ║        55 ║ 55555 ║
║  6  ║      4 ║      7 ║        66 ║ 66666 ║
╚═════╩════════╩════════╩═══════════╩═══════╝

The requirement is
1. Join two table
2. Group By C_ID
3. The latest rows in Group By

I tried to modify the answer By Bill Karwin from How do I join the most recent row in one table to another table?

SELECT e.*, s1.*  
FROM table_a e   
INNER JOIN
table_b s1
    ON (e.p_id = s1.p_id)   
LEFT OUTER JOIN table_b s2
    ON (e.p_id = s2.p_id AND s1.id < s2.id) 
WHERE s2.p_id IS NULL;

but I could not achieve what I want. From his answer I will get

╔═════╦════════╦════════╦═══════════╦═══════╦═════════╗
║ ID  ║  P_ID  ║  C_ID  ║  C_PRICE  ║  TIME ║  P_NAME ║
╠═════╬════════╬════════╬═══════════╬═══════╬═════════╣
║  1  ║      1 ║      3 ║        11 ║ 11111 ║ name1   ║
║  2  ║      2 ║      4 ║        22 ║ 22222 ║ name2   ║
║  5  ║      3 ║      6 ║        55 ║ 55555 ║ name3   ║
║  6  ║      4 ║      7 ║        66 ║ 66666 ║ name5   ║
╚═════╩════════╩════════╩═══════════╩═══════╩═════════╝

But the output what I want is as below ( dublicate P_ID is ok but for each dublicate P_ID should not have dublicate C_ID )

╔═════╦════════╦════════╦═══════════╦═══════╦═════════╗
║ ID  ║  P_ID  ║  C_ID  ║  C_PRICE  ║  TIME ║  P_NAME ║
╠═════╬════════╬════════╬═══════════╬═══════╬═════════╣
║  1  ║      1 ║      3 ║        11 ║ 11111 ║ name1   ║
║  2  ║      2 ║      4 ║        22 ║ 22222 ║ name2   ║
║  3  ║      3 ║      5 ║        33 ║ 33333 ║ name3   ║
║  5  ║      3 ║      6 ║        55 ║ 55555 ║ name3   ║
║  6  ║      4 ║      7 ║        66 ║ 66666 ║ name5   ║
╚═════╩════════╩════════╩═══════════╩═══════╩═════════╝
Community
  • 1
  • 1
cjmling
  • 6,896
  • 10
  • 43
  • 79

2 Answers2

1
SELECT e.*, s1.*
FROM table_a e INNER JOIN 
(SELECT * FROM (SELECT * FROM table_b ORDER BY time DESC) temp GROUP BY c_id) s1;

The innermost ORDER BY ensures the for the same c_id the desired row always comes first, the outter GROUP BY will group on c_id, and having not specified otherwise will return the first row found for each group.

Levente Pánczél
  • 1,895
  • 2
  • 14
  • 16
  • I assumed by "the latest" you mean the field time, but now I see you used id. If id is good for you then use that instead of time (especially if it happens to be a primary key, as the name suggests). – Levente Pánczél Dec 07 '12 at 08:36
  • ahhh finally... u forget the WHERE clause WHERE e.P_ID = s1.P_ID; http://sqlfiddle.com/#!2/38554/15 – cjmling Dec 07 '12 at 10:49
0

You must

  1. Join two table
  2. Group By C_ID
  3. Number row for each group desc ordered by chosen field (last imply order!)
  4. Filter by row number = 1.

This can help you for numbering rows.

Hamlet Hakobyan
  • 32,965
  • 6
  • 52
  • 68