[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 94
  • Last Modified:

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

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
John Ellis
Asked:
John Ellis
1 Solution
 
John EllisAuthor Commented:
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
 
PortletPaulCommented:
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
 
Olaf DoschkeSoftware DeveloperCommented:
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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now