Solved

Getting every other record of a table.

Posted on 2006-11-28
3
506 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 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

Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

705 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