-2

The table structure to solve

| **Email** |  **Product name** |  **price** |    **Creatdt**   |
|R@gmail.com| ABC               | 9.99       |05/07/19 12:30:21 |
|R@gmail.com| efg               | 12.99      |05/07/19 12:30:21 |
|R@gmail.com| xyz               | 15.99      |05/07/19 12:30:21 |
|M@gmail.com| ABC               | 9.99       |05/07/19 12:30:21 |
|M@gmail.com| efg               | 12.99      |05/07/19 12:35:42 |

1 record for 1 email tried to execute below query

Select Z from(  
Select rank() over (PARTITION BY Email ORDER BY CreateDt desc) AS RN,
P.email,
P.product name,
P.price,
p.Creatdt
From product table P
)Z
Where Z.RN = 1

After executing the ranking query the result

| **Email** | **Product name** | **price**|    **Creatdt**   |
|R@gmail.com| ABC              | 9.99     |05/07/19 12:30:21 |
|R@gmail.com| efg              | 12.99    |05/07/19 12:30:21 |
|R@gmail.com| xyz              | 15.99    |05/07/19 12:30:21 |
|M@gmail.com| efg              | 12.99    |05/07/19 12:35:42 |

So I need only 1 R@gmail.com record, and want to prioritize based on product name.

  1. CASE 1 If product name is ABC then select that record with ABC as product name in the output table by excluding the records with efg and xyz.
  2. CASE 2 if there are two same records with efg and xyz then I want to select xyz as product name record to the destination table excluding efg record.

The table output should be

| **Email** | **Product name** | **price** |    **Creatdt**   |
|R@gmail.com| ABC              | 9.99      |05/07/19 12:30:21 |
|M@gmail.com| efg              | 12.99     |05/07/19 12:35:42 |
  • Could some one help in the query for prioritize based on the product name to select the record based on the product name when email and date is exactly same. Thank you – Rick May 8 '18 at 18:21
2

Sounds like you just need to add more columns to the ORDER BY clause in the RANK function:

SELECT Z.* 
FROM(   SELECT RANK() OVER( PARTITION BY Email 
                            ORDER BY CreateDt DESC, 
                                     CASE 
                                        WHEN product_name = 'abc' THEN 1
                                        WHEN product_name = 'xyz' THEN 2
                                        ELSE 3
                                     END) RN,
                P.email,
                P.product_name,
                P.price,
                p.Creatdt
        FROM product_table P)Z
WHERE Z.RN = 1
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.