cancel
Showing results for 
Search instead for 
Did you mean: 

HANA SQL Join only first entry of another table

Former Member
0 Kudos

http://stackoverflow.com/questions/6792285/sql-distinct-based-on-only-some-columns

Hi Folks,

I got the requirement to join from another table only the first matching entry (as the second table contains several records that match the criteria)

Something similar like described in the above link.

However, when I implement the inner join with the WHERE condition and select top 1 it complains thatTOP cannot be used in subquery.

Does anybody know how to solve this?

Cover the requirement differently?

Thanks for all replies in advance.

Best regards,

Axel

Accepted Solutions (1)

Accepted Solutions (1)

former_member182302
Active Contributor
0 Kudos

In the same link, using Window functions you have an answer, did you try that? it should work

Regards,

Krishna Tangudu

Former Member
0 Kudos

Hi Krishna,

are you referring to the code listed as second code box using ROW_NUM() and rank?

(stupid copy and past to this post is not working for me, and drives me crazy)

I though ROW_NUM() is not available in HANA.

Also the "order by" was listed as "complain" in sub-query in the error where it complained about top so I did not try that at all.

Can you clarify?

Best regards,

Axel

former_member182302
Active Contributor
0 Kudos

Row Number, First all of them are available have a look on this link:

Window Functions - SAP HANA SQL and System Views Reference - SAP Library

Regards,

Krishna Tangudu

Answers (1)

Answers (1)

lbreddemann
Active Contributor
0 Kudos

This is possible without the relatively costly window function.

The key here is to see that "returning only the first row" is a form of aggregation.

Since there is no native "FIRST()" aggregation function available, you have to define what should be the first detail record.

You can do that e.g. by using the MAX() function on the detail record key and group by the header reference key.


select h.*, det_oneline.*

from

header h inner join

  (select distinct d_det.*

  from detail d_det

  where

  (d_det.head_id, d_det.detail_id) IN ( /* SINGLE line of details*/

                                     select d.head_id, max(d.detail_id) detail_id

                                     from detail d

                                     group by d.head_id)) as det_oneline

    on h.head_id = det_oneline.head_id

order by h.head_id  asc  ;    

The next step is then to pick up the detail information for the single line items and join that to the header table.