sql server programming: I want to flatten a table of sequential values for display on a web page. My
data table looks something like this:

Create Table mydata (
Mykey int NOT NULL PRIMARY KEY,
Wiget int,
theValue char(3),
dateRecorded D)

Values for each widget can be recorded at random times. I want flatten the
last 5 recordings out and display like this:

Widget1 : 17-10/21/06 : 21-10/17/06 : 13-10/15/06 : etc…
Widget2 : 20-10/28/06 : etc…
etc…

I assume the best way is kind of like creating a quarterly sales report
where you create a table, sum and group the sales figures by quarter and then
insert into your flattened structure. Of course you can derive the quarter
column from the date and then insert your results.

In this case I will want to sort and number each result 1 - 5 descending by
date so it looks like this:

1 Wiget1 17 10/21/06
2 Wiget1 21 10/17/06
3 Wiget1 13 10/15/06
4 Wiget1 07 10/21/06
5 Wiget1 02 10/21/06
1 Wiget2 20 10/28/06
2 Wiget2 28 10/22/06
Etc…

What’s the best way to create this number column when I do the query to get
the top 5 rows for each widget?

select count(*) as Seq,
a.Wiget,
a.theValue,
a.dateRecorded
from mydata a
inner join mydata b on b.Wiget=a.Wiget
and (b.dateRecorded>a.dateRecorded
or (b.dateRecorded=a.dateRecorded and
b.Mykey<=a.Mykey))
group by a.Wiget,a.theValue,a.dateRecorded
having count(*)<=5
order by a.Wiget,count(*)

For some basic ideas see:
http://www.projectdmx.com/tsql/ranking.aspx

--
Anith