make a record display the numbers

I am looking for a query that counts the amount of pictures the user has in
the table, then select the 1st 20 pictures and create a record number. e.g.,
results would be summit like = 1, ~/pix/1.jpg, mypic. where 1 is the
autonumber from the count and ~/pix/1.jpg is the image location and mypic is
the caption. the table select script is below with all the colums to be
returned.
SELECT Pic, Cap
FROM Pics
WHERE UName = @.UName
there are only two columns in the table except for the key which is pId.
If anyone can help I would be grateful.Wow, this has come up a half dozen times day.
First off, how do you define the "first" 20 pictures?
SELECT TOP 20 Pic, Cap
FROM Pics
WHERE UName = @.UName
ORDER BY ...'...
Next, why can't these rankings be generated by the presentation layer (e.g.
ASP code or what have you), which has to loop through each row anyway,
instead of forcing the database to /also/ do this iteration?
http://www.aspfaq.com/2427
"Eamon" <eamon@.nuvola.co.uk> wrote in message
news:%23weQ$ppoFHA.3408@.tk2msftngp13.phx.gbl...
>I am looking for a query that counts the amount of pictures the user has in
> the table, then select the 1st 20 pictures and create a record number.
> e.g.,
> results would be summit like = 1, ~/pix/1.jpg, mypic. where 1 is the
> autonumber from the count and ~/pix/1.jpg is the image location and mypic
> is
> the caption. the table select script is below with all the colums to be
> returned.
> SELECT Pic, Cap
> FROM Pics
> WHERE UName = @.UName
> there are only two columns in the table except for the key which is pId.
> If anyone can help I would be grateful.
>|||Eamon, what determines "first"?
If it's pId, then:
DECLARE @.T TABLE
(
rn INT NOT NULL IDENTITY,
Pic <datatype>,
Cap <datatype>
);
INSERT INTO @.T
SELECT Pic, Cap
FROM Pics
WHERE UName = @.UName
ORDER BY pId;
SELECT * FROM @.T;
BG, SQL Server MVP
www.SolidQualityLearning.com
"Eamon" wrote:

> I am looking for a query that counts the amount of pictures the user has i
n
> the table, then select the 1st 20 pictures and create a record number. e.g
.,
> results would be summit like = 1, ~/pix/1.jpg, mypic. where 1 is the
> autonumber from the count and ~/pix/1.jpg is the image location and mypic
is
> the caption. the table select script is below with all the colums to be
> returned.
> SELECT Pic, Cap
> FROM Pics
> WHERE UName = @.UName
> there are only two columns in the table except for the key which is pId.
> If anyone can help I would be grateful.
>
>|||I meant, SELECT TOP 20 Pic, Cap...
BG, SQL Server MVP
www.SolidQualityLearning.com
"Itzik Ben-Gan" wrote:
> Eamon, what determines "first"?
> If it's pId, then:
> DECLARE @.T TABLE
> (
> rn INT NOT NULL IDENTITY,
> Pic <datatype>,
> Cap <datatype>
> );
> INSERT INTO @.T
> SELECT Pic, Cap
> FROM Pics
> WHERE UName = @.UName
> ORDER BY pId;
> SELECT * FROM @.T;
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Eamon" wrote:
>