Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

My ultimate goal is to select every second row (by primary key order) without any additional columns in the result set. I want to do any manipulation in dbisql but the final SELECT will be in a MobiLink script. I need to repeat this on multiple tables and can make no assumptions about the pkey values or type. I can add a column to each table if needed.

Can it be done in a single select statement (without adding a column)? (I know SELECT * FROM Foo WHERE mod(number(*),2) = 0 ORDER BY num does not work)

If I add a column to all the tables, can a single update be written to update every second column?

If the rows are inserted in primary key order, chances are adding a column with default autoincrement should number them in order and then I select every odd or even value but there is no guarantee of the order.

I'm working with SQL Anywhere 11 in this particular problem.

asked 23 Jun '11, 10:50

PhilippeBertrand%20_SAP_'s gravatar image

accept rate: 22%

edited 23 Jun '11, 11:08


Yes, I could have just walked down the hallway but when I first pondered this, the first answers I got weren't satisfactory. Besides, there aren't enough question here - perhaps its just because our docs are so great!

(23 Jun '11, 11:54) PhilippeBert...

After messing up the syntax a few times, I got a single SELECT to work:

   (SELECT num, MyID, row_number() OVER( ORDER BY num ) From Foo) AS Ba(num,MyID,bar)
WHERE mod(,2) = 0

Still curious if a single UPDATE could be written to update every second row.

permanent link

answered 23 Jun '11, 11:26

PhilippeBertrand%20_SAP_'s gravatar image

accept rate: 22%


The update part is simple once you have the query that selects the PKs of the rows to be updated - the general form is:

UPDATE T SET ... WHERE in ( <your-query-that-selects-the-pks-to-be-updated> )

(23 Jun '11, 13:13) Mark Culp
Replies hidden

Yes, now that I figured out how to control the select list, it is obvious.

(23 Jun '11, 13:17) PhilippeBert...
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 23 Jun '11, 10:50

question was seen: 1,483 times

last updated: 23 Jun '11, 13:17