You can translate the question and the replies:

Need to extract the second row of a duplicate in a table

Hi Folks! I have a scenario where I have a list of customers with a driver number. In some instances, the customer is duplicated because of a second driver being assigned to the customer. Such as this; Customer Driver 101369 5058 101369 5602 How do I go about selecting the SECOND row of this query for every customer that has this scenario? Any insights would be greatly appreciated!
user
19-10-2020 04:48:49 -0400

1 Answer

Hi, You can use analytical functions like [ROW_NUMBER()](https://community.denodo.com/docs/html/browse/latest/vdp/vql/appendix/syntax_of_condition_functions/analytic_functions_window_functions#row-number) to achieve this. If the underlying data source supports such analytical functions then Virtual DataPort will delegate these functions to the data source. If the underlying data source does not support such functions take a look at [DataMovement optimization](https://community.denodo.com/docs/html/browse/latest/vdp/vql/appendix/syntax_of_condition_functions/analytic_functions_window_functions#workaround-to-execute-analytic-functions) to see if the workaround is possible. If a customer can have a maximum of 2 records with different driver values then the below query can be used. Assume the view has 2 integer columns – customer & driver. ``` select b.customer,b.driver from bv_customer_driver b join ( select customer,max(driver) driver from bv_customer_driver group by customer) a on b.customer = a.customer and b.driver = a.driver; ``` You can also use the query above to create a derived view. Have a look at [Defining a Derived View](https://community.denodo.com/docs/html/browse/latest/vdp/vql/defining_a_derived_view/defining_a_derived_view) Virtual DataPort guide for more details. Hope this helps!
Denodo Team
 Edited on: 20-10-2020 08:08:02 -0400
You must sign in to add an answer. If you do not have an account, you can register here