Monday, June 13, 2011

[GENERATING PAGE NUMBER IN SELECT QUERIES] I want to give a group number to rows, which will change on every 10th row. e.g. First 10 rows grp no 0, next 10 rows grp no 1, and so on. How can I do this ?



Give Row number to each row ( by using ROW_NUMBER)  , then divide this row_number by a particular no  say 10 to get grp no.


( Note that this 10 can be any number , it will be the group or page size)
select fuellevel , DIFF , timeparts , row_number() over ( order by timeparts ) as rownum , row_number() over ( order by timeparts ) /10 as grp from RSCFuelChart
e.g


fuellevelDIFFtimepartsrownumgrp
1.0207:20:1710
1.0207:20:5020
1.0207:21:2030
1.0207:21:5040
1.0207:22:2050
1.0207:22:5060
1.0207:23:2070
1.0207:23:5080
1.0207:24:1890
1.0207:24:51101
1.0207:25:21111
1.0207:25:51121
1.0207:26:21131
1.0207:26:51141
1.0207:27:21151
1.0207:27:48161
1.0207:28:22171
1.0207:28:52181
1.0207:29:22191
1.0207:29:52202
1.0207:30:22212
1.0207:30:52222
1.0207:30:52232
1.0207:31:22242
1.0207:31:53252
1.0207:32:23262
1.0207:32:53272
1.0207:33:23282
1.0207:33:53292
1.0207:34:23303
1.0207:34:53313
1.0207:35:21323
1.0207:35:54333
1.0207:36:24343




This can be very useful in smoothning the graphs, e.g. following query takes avg of every 10 records and max(time) of these 10 records.

select AVG(a.fuellevel) as fiellevel, MAX(timeparts) as timeparts from ( select fuellevel , DIFF , timeparts , row_number() over ( order by timeparts ) as rownum , row_number() over ( order by timeparts ) /10 as grp from RSCFuelChart ) A group

this essentially reduces no of recs from say 500 in all to 50 only to be plotted.
by a.grp


==============================
So to summarize :


declare @pagesize int = 20 , @pagenumber int = 1select * from ( select fuellevel , DIFF , timeparts , row_number() over ( order by timeparts ) as rownum from RSCFuelChart ) A where ((a.rownum /@pagesize) + 1) = @pagenumber

No comments:

Post a Comment