Getting every other record of a table.

I want to have two queries written in standard sql (no "declare", no cursors etc.).

The first query has to return record no 1, 3, 5, 7 ... etc... - of a table containing names that will have to be sorted alphabetically.
The second has to return record 2, 4, 6, 8 ... etc... - of the same table.

There is no incrementally numbered id.

The result should look something like this:

result query1        result query 2

        aa                     ab
        ba                     bb
        ca                     cb
        cc                     cd

etc...
The query has to be written in standard SQL because it is supposed to be used in a gridview in MSSQL 2005 without using stored procedures...

Can it be done?

Rune
RunePerstrupAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>it is supposed to be used in a gridview in MSSQL 2005 without using stored procedures...
why without a stored procedure? a gridview should be able to be fed from a stored procedure?
or what am I missing...

anyhow:

select * from (
select t.*, row_number() over (order by somefield ) r from yourtable t
) as l
where r % 2 = 1

and


select * from (
select t.*, row_number() over (order by somefield ) r from yourtable t
) as l
where r % 2 = 0
0
 
RunePerstrupAuthor Commented:
Because I only get Lots of trouble when i try use input and output variables. I just can't get it to work - and i have a deadline on this little thing, so it's really nice to avoid the problem for now.

Thx for your fast response. It is very appreciated. I didn't know of the the row_number() function ;-)

Great!
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I didn't know of the the row_number() function ;-)
it's one of the nice little new things in sql server 2005
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.