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.

paging query in sql server compact edition

Hi,

I want to write query to implement paging in sql server ce 3.0. But it seems it does not support TOP or LIMIT keyword.

Can someone suggest alternative way to write a custom paging query in sql server ce 3.0.

Thanks,

van_03

You are correct, there is no intrisic support for paging the results from the query processor. What I do is use the SqlCeResultSet which has the ability to page through a range or records once you create the result set.

Darren

|||

Actually I have about 1 million rows in my table the sql sercer ce database. The table has no numeric field and no identity column either. I want to pick chunks of rows, may be about 100000 at a time. Then next chunk and so on. Can you suggest some way of acheiving this ?

Thanks,

van_03

Pagination with SP

I'd like to write an SP with parameters that returns specified groups rows
in ordered groups of arbitrary size. Kind of a "sliding" TOP. Like, show me
the first 40 items in a list, show me the 4th group of 40 items in a list,
show me the Nth group of X items, etc. I see this kind of thing all the time
and would prefer not to have to deal with the pagination in VB or ASP, or
get all the rows and then filter out what I don't want to display.
So, I'd like to pass in to the SP parameters for the size of each grouping
and an ordinal for the group I wanted.
CREATE PROCEDURE pagination_sp
@.ordinal int,
@.groupsize int
AS
SELECT LastName, FirstName
FROM People
ORDER BY LastName, FirstName
Thanks for any tips on how to approach this.Don
http://www.aspfaq.com/show.asp?id=2120
"Don Miller" <nospam@.nospam.com> wrote in message
news:%23oKvuTlSFHA.2784@.TK2MSFTNGP12.phx.gbl...
> I'd like to write an SP with parameters that returns specified groups rows
> in ordered groups of arbitrary size. Kind of a "sliding" TOP. Like, show
me
> the first 40 items in a list, show me the 4th group of 40 items in a list,
> show me the Nth group of X items, etc. I see this kind of thing all the
time
> and would prefer not to have to deal with the pagination in VB or ASP, or
> get all the rows and then filter out what I don't want to display.
> So, I'd like to pass in to the SP parameters for the size of each grouping
> and an ordinal for the group I wanted.
> CREATE PROCEDURE pagination_sp
> @.ordinal int,
> @.groupsize int
> AS
> SELECT LastName, FirstName
> FROM People
> ORDER BY LastName, FirstName
> Thanks for any tips on how to approach this.
>

Page reads/sec just shows 0

I am trying to monitor the reads and write of my SQL-Server 2000 EE with
Performance Monitor on Windows 2000 AS. I can monitor some counters of
SQLServer:BufferManager just fine (for example Buffer Cache Hit ratio),
but the counters Page reads/sec, Pageahead pages/sec, Page writes/sec,
Checkpoint pages/sec (and maybe others) are not showing just 0.
Does anyone know have to get proper data?
Thanks
Gert-Jan
never mind... it was in fact not reading or writing... This box sure has
a lot of memory!
Gert-Jan
Gert-Jan Strik wrote:
> I am trying to monitor the reads and write of my SQL-Server 2000 EE with
> Performance Monitor on Windows 2000 AS. I can monitor some counters of
> SQLServer:BufferManager just fine (for example Buffer Cache Hit ratio),
> but the counters Page reads/sec, Pageahead pages/sec, Page writes/sec,
> Checkpoint pages/sec (and maybe others) are not showing just 0.
> Does anyone know have to get proper data?
> Thanks
> Gert-Jan

Page reads/sec just shows 0

I am trying to monitor the reads and write of my SQL-Server 2000 EE with
Performance Monitor on Windows 2000 AS. I can monitor some counters of
SQLServer:BufferManager just fine (for example Buffer Cache Hit ratio),
but the counters Page reads/sec, Pageahead pages/sec, Page writes/sec,
Checkpoint pages/sec (and maybe others) are not showing just 0.
Does anyone know have to get proper data?
Thanks
Gert-Jannever mind... it was in fact not reading or writing... This box sure has
a lot of memory!
Gert-Jan
Gert-Jan Strik wrote:
> I am trying to monitor the reads and write of my SQL-Server 2000 EE with
> Performance Monitor on Windows 2000 AS. I can monitor some counters of
> SQLServer:BufferManager just fine (for example Buffer Cache Hit ratio),
> but the counters Page reads/sec, Pageahead pages/sec, Page writes/sec,
> Checkpoint pages/sec (and maybe others) are not showing just 0.
> Does anyone know have to get proper data?
> Thanks
> Gert-Jan