OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register] 
 [Profile]   [Log in to check your private messages]   [Log in] 

query so show ony first or last record -SOLVED

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
cilt
Power User
Power User


Joined: 20 May 2010
Posts: 89
PostPosted: Sun Sep 12, 2010 7:06 pm    Post subject: query so show ony first or last record -SOLVED Reply with quote
can I make a query that will display only the last record in a table

using the GUI ?

or some other way ?

Last edited by cilt on Sun Sep 12, 2010 8:39 pm; edited 1 time in total
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2474
Location: 3rd Rock From The Sun
PostPosted: Sun Sep 12, 2010 8:03 pm    Post subject: Reply with quote
cilt:

You said / asked:

cilt wrote:
can I make a query that will display only the last record in a table

using the GUI ?

or some other way ?


The answer is, probably yes, BUT, I use the word probably, because I am not 100% certain I understand the purpose and what you want.

Let me attempt to explain a few thing . . . if this does not help, maybe if you can be clearer of what you want.

  1. A thing about a database Query . . . it is composed by a a SELECT statement ( the statement starts with the word SELECT ).

  2. You CAN ( have the ability ) SORT ( Query key word of ORDER ) by any field, or, combination of fields.

  3. The order a Query will come back . . . is 'arbitrary' . . . UNLESS you define a specific order.

  4. You can create a Query with the GUI ( Graphic User Interface ), and, define SORT ( ORDER by clause ) for the 'sequence' of the rows returned.

  5. Therefore, you could have TWO Queries, one that SORTS in ASCENDING order, and, a SECOND that SORTS in DESCENDING order. That way, the FIRST record of one Query, will be same as the LAST record of the second Query. Smile

  6. The OpenOffice BASE parser, does NOT allow the ability to 'control' the number of records returned ( that is the 'bad' news ) . . . BUT . . . the database driver . . . probably HSQL . . . DOES have an option to 'restrict' the number of records to be returned. For example, it could be 1, or 25, or 100 . . . whatever.

  7. The SYNTAX to return only ONE record , verses, 25, or, 100 is:

    Code:
    LIMIT 1

    LIMIT 25

    LIMIT 100


  8. Therefore, if you first create your Query using the GUI ( Graphic User Interface ) . . . and next, choose EITHER:

    1. On the TOOLBAR . . . press the icon . . . Switch Design View On/Off ( yellow triangle at the southwest corner )

    2. Menu: View -> Switch Design View On/Off


    all you have to do is . . . at the end add the LIMIT 1 as I identified above.

  9. Next, to STOP the OpenOffice Base Parser from saying an error, either:

    1. On the Toolbar, press the icon: Run SQL command directly ( SQL with a green check mark )

    2. From the Menu: Edit -> Run SQL command directly


  10. Run the Query ( F5, toolbar icon Run Query )

  11. Save the Query

  12. Say, "Gee Sliderule, that was easy. Thanks. Now, all I have to do is mark this forum post as . . . [Solved] on my first post Title. Smile


Summary: Yes, you can return 1 or any specific number of records . . . and . . . by having TWO Queries, with OPPOSITE sort ( ORDER ) definitions . . . AND by passing the SQL directly to the database engine . . . I think your needs are met. Smile

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
cilt
Power User
Power User


Joined: 20 May 2010
Posts: 89
PostPosted: Sun Sep 12, 2010 8:40 pm    Post subject: Reply with quote
Thanks for that it works with views as well Very Happy
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2474
Location: 3rd Rock From The Sun
PostPosted: Sun Sep 12, 2010 8:49 pm    Post subject: Reply with quote
Glad things are working for you.

Just for others reading this . . . about the OpenOffice Base Parser . . . since the Parser will NOT be used . . . that means you also do NOT have the ability to define the Query as a Parameter Query.

That means, you will NOT be able to 'prompt' a user for input . . . AND . . . include a LIMIT clause. Also, since the Parser will not be used, any DATES will have to be defined in YYYY-MM-DD format . . . but . . . if you first use the GUI ( Graphic User Interface ) to include a date in 'local' format . . . it translates the date to database format.

Sliderule
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group