The forum will be down for scheduled maintenance on Saturday, March 4 beginning at 10am EST. Actual downtime is unknown but may be several hours.

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

PhilippeBert...
1.4k41733
accept rate: 22%

edited 23 Jun '11, 11:08

3

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 Ba.num, Ba.MyID FROM
   (SELECT num, MyID, row_number() OVER( ORDER BY num ) From Foo) AS Ba(num,MyID,bar)
WHERE mod(Ba.bar,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

PhilippeBert...
1.4k41733
accept rate: 22%

3

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 T.pk in ( <your-query-that-selects-the-pks-to-be-updated> )

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

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

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "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:

×137

question asked: 23 Jun '11, 10:50

question was seen: 835 times

last updated: 23 Jun '11, 13:17