Solved

# Select Top row from child table

Posted on 2011-03-14
486 Views
I am writing a sproc to return rows from tableA and I need the first row that was entered in child tableB.

TableA
A_ID              PK Identity
JobNumber

TableB
B_ID                 PK Identity
A_ID                 FK  int
DateEntered
Comment

I am close but not there yet. Since the PK in tableB is an identity I think I'm safe selecting the row that has the min(B_ID) WHERE tableB.A_ID = tableA.A_ID

So I have this
SELECT * FROM tableA, (SELECT DateEntered,Comment FROM tableB  WHERE tableB.A_ID = tableA.A_ID)

Hung up getting the rest. Still trying but would like some help.
0
Question by:AkAlan
• 3

LVL 26

Expert Comment

ID: 35133883
SELECT A.*, B. DateEntered, B.Comment
FROM tableA A
INNER JOIN (SELECT A_ID, MIN(DateEntered) AS DateEntered, MIN(Comment) AS Comment FROM tableB GROUP BY A_ID) B ON A.A_ID = B.A_ID
0

LVL 32

Expert Comment

ID: 35133986

SELECT * FROM tableA, (SELECT top 1 DateEntered,Comment FROM tableB WHERE tableB.A_ID = tableA.A_ID order by dateentered)
0

LVL 32

Expert Comment

ID: 35134028
Posted the wrong one

SELECT *, (SELECT top 1 DateEntered,Comment FROM tableB WHERE tableB.A_ID = tableA.A_ID order by dateentered)
FROM tableA
0

LVL 32

Expert Comment

ID: 35134059

Disregard my post
0

LVL 40

Accepted Solution

Sharath earned 500 total points
ID: 35134150
try this query.
``````SELECT *
FROM (SELECT t1.A_ID,t1.JobNumber,t2.B_ID,t2.DateEntered,t2.Comment,
ROW_NUMBER()
OVER(PARTITION BY t1.A_ID ORDER BY t2.B_ID) rn
FROM TableA t1
JOIN TableB t2
ON t1.A_ID = t2.A_ID) t1
WHERE rn = 1
``````
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…