I think the RelNode you build by RelBuilder is equivalent to SELECT
DISTINCT FROM ORDER BY, except that the generated ORDER BY condition is
different.
-Rui
On Tue, Aug 4, 2020 at 3:40 PM Hrudaya Reddy <hre...@csod.com.invalid>
wrote:
> 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 <amaliu...@apache.org>
> Sent: Monday, August 3, 2020 10:08 PM
> To: dev@calcite.apache.org
> 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 <hre...@csod.com.invalid>
> 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
>
