Thanks for your input Julian and Rui.
Regards,
Hrudaya
-----Original Message-----
From: Julian Hyde <jh...@apache.org>
Sent: Thursday, August 6, 2020 10:03 AM
To: dev@calcite.apache.org
Subject: [EXTERNAL] Re: Re: Implementation of DISTINCT and ORDER BY
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 <amaliu...@apache.org> 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 <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
> >
----------------------------------------------------------------------
*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
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.
