Thanks a lot for your input Rui.
This is how I am implementing it.
RelNode relNode = builder
.scan("users")
.project(builder.field("name_first"),
builder.field("name_last"))
.distinct()
.sort(builder.desc(builder.field("name_last")))
.build();
and the query generated is
SELECT [name_first], [name_last]
FROM [users]
GROUP BY [name_first], [name_last]
ORDER BY CASE WHEN [name_last] IS NULL THEN 0 ELSE 1 END, [name_last] DESC
So, just wanted to make sure that this is the right approach.
Regards,
Hrudaya
-----Original Message-----
From: Rui Wang <[email protected]>
Sent: Monday, August 3, 2020 10:08 PM
To: [email protected]
Subject: [EXTERNAL] Re: Implementation of DISTINCT and ORDER BY
I did a test locally and I found your example syntax is correct.
I used "select distinct deptno, empno from emp order by empno desc"
and Calcite generates this plan:
LogicalSort(sort0=[$1], dir0=[DESC])
LogicalAggregate(group=[{0, 1}])
LogicalProject(DEPTNO=[$7], EMPNO=[$0])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
What error message did you get?
-Rui
On Mon, Aug 3, 2020 at 9:52 PM Hrudaya Reddy <[email protected]> wrote:
>
> Hi all,
>
> I am trying to generate the following SQL query but I am facing some
> difficulties in finding the right syntax to implement 'DISTINCT' and 'ORDER
> BY'
>
> SELECT DISTINCT name_first, name_last FROM users ORDER BY name_last DESC
>
>
> I would really appreciate it if you could guide me with the correct usage.
>
>
>
> Thanks in advance.
>
> Regards,
> Hrudaya
>
> This message, together with any attachments, is intended only for the use of
> the individual or entity to which it is addressed and may contain
> confidential and/or privileged information. If you are not the intended
> recipient(s), or the employee or agent responsible for delivery of this
> message to the intended recipient(s), you are hereby notified that any
> dissemination, distribution or copying of this message, or any attachment, is
> strictly prohibited. If you have received this message in error, please
> immediately notify the sender and delete the message, together with any
> attachments, from your computer. Thank you for your cooperation.
----------------------------------------------------------------------
*EXTERNAL MESSAGE WARNING: This email originated from outside of Cornerstone.
Do not click links or open attachments unless you recognize the sender and know
the content is safe. Please see this wiki for more information on email safety:
https://wiki.cornerstoneondemand.com/display/ISS/Security+Awareness
