[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Select Top row from child table

Posted on 2011-03-14
5
Medium Priority
?
504 Views
Last Modified: 2012-05-11
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
Comment
Question by:AkAlan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 26

Expert Comment

by:tigin44
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

by:Ephraim Wangoya
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

by:Ephraim Wangoya
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

by:Ephraim Wangoya
ID: 35134059

Disregard my post
0
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 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

Open in new window

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question