Trafodion

Using FIRST #N to limit select results

Asked by Tung Jin Chew

Hi,

I'm using Trafodion 0.8.3, and trying to limit the number of results returned from 'trafci'.

When running
  SELECT * from test1;

  F1 F2
  ----------- -----------

            3 3
            3 3
            1 1
it works, but running
  SELECT FIRST 1 * from test1;
gives an error
  *** ERROR[15001] A syntax error occurred at or before:
  SELECT FIRST 1 * from test1;
             ^ (12 characters from start of SQL statement) [2014-08-04 18:27:49]

I was expecting it to only return the first result. Is this feature not implemented yet? It's listed in
  https://wiki.trafodion.org/wiki/index.php/Release_0.8.0_Features
and on page 76 of the Trafodion SQL Reference Manual .

Thanks,
Jin

Question information

Language:
English Edit question
Status:
Solved
For:
Trafodion Edit question
Assignee:
No assignee Edit question
Solved by:
Dave Birdsall
Solved:
Last query:
Last reply:
Revision history for this message
Dave Birdsall (dave-birdsall) said : 		#1

Hi,

I believe the proper syntax is:

select [first 1] * from test1;

Thanks,

Dave

Revision history for this message
Dave Birdsall (dave-birdsall) said : 		#2

Hi,

One further comment: I see the manual documentation is a bit confusing because the square bracket is usually used as a meta-character. But for [first n] and [last n] it is meant literally. I've filed a bug 1352479 to clarify the documentation.

Thanks,

Dave

Revision history for this message
Tung Jin Chew (tung-jin-chew) said : 		#3

Thank you! That works, I didn't realize the square brackets were literal since they were also used to indicate optional components as well.
  SELECT [[ANY N] | [FIRST N]] [ALL | DISTINCT] select-list
  FROM table-ref [,table-ref]...

Tung Jin Chew | Software Developer, Autonomy | HP Software | <email address hidden>

________________________________________
From: <email address hidden> [<email address hidden>] on behalf of Dave Birdsall [<email address hidden>]
Sent: 04 August 2014 18:37
To: Chew, Tung Jin
Subject: Re: [Question #252619]: Using FIRST #N to limit select results

Your question #252619 on Trafodion changed:
https://answers.launchpad.net/trafodion/+question/252619

    Status: Open => Answered

Dave Birdsall proposed the following answer:
Hi,

I believe the proper syntax is:

select [first 1] * from test1;

Thanks,

Dave

--
If this answers your question, please go to the following page to let us
know that it is solved:
https://answers.launchpad.net/trafodion/+question/252619/+confirm?answer_id=0

If you still need help, you can reply to this email or go to the
following page to enter your feedback:
https://answers.launchpad.net/trafodion/+question/252619

You received this question notification because you asked the question.

Revision history for this message
Best Dave Birdsall (dave-birdsall) said : 		#4

Hi,

We both had the same thought simultaneously. I've filed bug 1352479 to clarify the documentation.

Thanks,

Dave

Revision history for this message
Tung Jin Chew (tung-jin-chew) said : 		#5

Okay, that makes sense, thanks again for your help!

Revision history for this message
Anoop Sharma (anoop-sharma) said : 		#6

Yes, that square bracket syntax could be confusing.

One difference between ANY N and FIRST N:

If your query has an order by and [FIRST N] is used, then rows will be sorted and first N sorted rows will be returned.
But with ANY N syntax, random N rows will be returned which may not necessarily be the sorted top N rows.

If query doesnt have an order by, then FIRST N and ANY N are the same.

Revision history for this message
Susan Sandstrom (susan-sandstrom) said : 		#7

Hi there,

I updated the [ANY N] | [FIRST N] syntax and description in the Trafodion SQL Reference Manual to be consistent with the "Notation Conventions" in the manual. Please see pages 16, 75, and 77 of the latest manual: http://docs.trafodion.org/Trafodion_SQL_Reference_Manual_0.8.0.pdf.

I hope that those changes in the manual make it clearer that square brackets ([]) are required in the ANY and FIRST clauses. If not, please let me know.

Thanks,

Susan