[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Getting every other record of a table.

Posted on 2006-11-28
3
Medium Priority
?
515 Views
Last Modified: 2008-03-06
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
0
Comment
Question by:RunePerstrup
  • 2
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 18028452
>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
 

Author Comment

by:RunePerstrup
ID: 18028510
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18029145
>I didn't know of the the row_number() function ;-)
it's one of the nice little new things in sql server 2005
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question