Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

T-SQL How to select the second value of a matching field in another table

Posted on 2016-08-27
3
Medium Priority
?
80 Views
Last Modified: 2016-08-30
Hello:

Table A contains a field called "DOCNUMBR", while Table B contains a field called "APFRDCNM".

In Table A, one row contains a value in "DOCNUMBR" that is in Table B's "APFRDCNM" field twice (i.e. the same value is in two rows).

For Table A's "DOCNUMBR", I want to select the second value in Table B's "APFRDCNM".

How do I do that?

Thank you!

John
0
Comment
Question by:John Ellis
[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 Comments
 

Author Comment

by:John Ellis
ID: 41773342
Hello:

I figured this out, by using the following:

select MIN(APFRDCNM) from RM20201 group by CUSTNMBR.

That really surprises me.  I needed the second "APFRDCNM" row.  I hope that makes sense.

Anyway, if it's the second row that I needed, then why did "MIN" work and not, say, "MAX"?

Thanks!

John
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41773428
John, without data to look at it's going to be darn hard to answer this.

You are also making the judgement of which row is "second" and we do not know the basis for this, but MIN() worked because the lowest value just happens to match your interpretation of "second".

btw: An alternative approach for the original question would be to use ROW_NUMBER()
1
 
LVL 30

Accepted Solution

by:
Olaf Doschke earned 2000 total points
ID: 41773523
It's not only likely but sure you'Re just lucky with MIN(), MIN() and MAX()´are about the min or max values in a group, not about min or max rownumber. There is no rownumber anyway, it's set theory in all it's beauty and ugliness at the same time, you only get rownumbers and could pick second row, if you use ROW_NUMBER(), as Paul states, and that also needs to know in which order to number rows.

The MSSQL database stores redcords in pages, but they are not book pages having any natural order, they are more like leaves of a tree, so before you define an ORDER by, there is no order of pages or table rows on these pages, as there is no order on caterpillars on the leaves of a tree.

So what works here is not a thing you learned and can apply in the same way on future problems. You rather only are getting the correct result, if the second row always contains the smallest APFRDCNM value per CUSTNMBR.

Bye, Olaf.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

609 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