on 12-16-2015 1:42 PM
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
In the same link, using Window functions you have an answer, did you try that? it should work
Regards,
Krishna Tangudu
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
87 | |
10 | |
10 | |
9 | |
7 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.