Pair of records

Hi folks,

I am trying to write a query to get data in pairs, for example, i have data like this:
sr_no week_no
1 24-A
2 24-B
3 24-C
4 25-A
5 25-B
6 26-A
7 26-B

I want to get data in pairs i.e. data for week_no 24-A and 24-B will come togather? is it possible?

Any urgent help will be highly appreicated.

Thanksany suggestion on my problem?|||possibly the reason no one answered within such a short time (it is courtesy not to bump your own question at all, but if you feel you must, give it more than just a few hours) is that your question is not very clear

here's one solution:create table pairs
( sr_no smallint not null
, week_no varchar(9)
)

insert into pairs values (1, '24-A')
insert into pairs values (2, '24-B')
insert into pairs values (3, '24-C')
insert into pairs values (4, '25-A')
insert into pairs values (5, '25-B')
insert into pairs values (6, '26-A')
insert into pairs values (7, '26-B')

select one.week_no
, two.week_no
from pairs one
, pairs two
where one.sr_no
= two.sr_no - 1
and floor(one.sr_no/2)
< floor((one.sr_no+1)/2)

24-A 24-B
24-C 25-A
25-B 26-A|||I am controlling data with Week numbers instead. So i am trying to get data of 24 and 25 togather and so one. Sr. Number is nothing but increament field.

Thanks for reply Mr. r937|||your data is very confusing

how about showing some rows of real data, and then show what results you expect

and which is it, oracle or sql server?|||here is your solution:select week_no/2
, amount
from yourtable
group
by week_no/2|||week_no/2?

You lost me on that one Rudy!

Mr roomi, I think your problem is that your table does not follow basic rules of normalization. You are trying to store two values ("24", and "A", for instance) in a single field. This makes it difficult to sort or group by the column's components.

Ideally, break the column into two different columns. If that is not an option, then you can simulate a "week_number" column using a formula:
Select left(week_no, charindex('-', week_no)-1) as week_number

(Double-check the syntax. I've been in Oracle recently and my TSQL is a little rusty...)|||week_no/2?

You lost me on that one Rudy!piece o' cake, my good man

divide the week number by 2, and because it's an integer, the result is an integer

so 22/2 is 11, 23/2 is 11, 24/2 is 12, 25/2 is 12, and so on

this allows you to group the pairs of weeks -- 22 with 23, 24 with 25, and so on

as for the normalization, check the other thread, it has better sample data, there's nothing wrong with the design|||aw crap, i meant to cross link the guy's other post and forgot to

http://www.dbforums.com/t1022906.html

guy has two identical threads going on in separate forums|||Jeez, a cross-post. This is a forum, not a freaking scavenger hunt.

I was baffled how you were going to divide the string "24-A" by two...

Mr. Roomi, here is a rule of thumb:
"You cannot code a problem clearly and simply until you can state the problem clearly and simply."

Really. I deal with people all too frequently who start coding without being able to define the problem, and they never get very far.