The CASE is to make null values collate first or last. I guess the target DB does not support NULLS LAST (or whatever) syntax. I think the behavior would be the same whether or not you have DISTINCT.
On Tue, Aug 4, 2020 at 4:42 PM Rui Wang <[email protected]> wrote: > > 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 <[email protected]> > 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 <[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 > >
