how can I create a rowcount in my query?

Hello everybody

I want to make a query like this:

SELECT TOP 15 * FROM TABLE

15 rows will return, but I would like a field that could by a countrow, is there any way to do this?

So I wouldl have a result like this:

1 house
2 paper
3 phone
4 chair

and so on

thanks
LVL 1
hidrauAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
with sql 2005:
SELECT TOP 15 t.*, ROW_NUMBER() OVER ( ORDER BY 1 ) r
  FROM TABLE t

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what version of sql server?
can you put the query into a stored procedure?
0
 
hidrauAuthor Commented:
2005
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
hidrauAuthor Commented:
no, I can't use a store proc
0
 
RamanhpCommented:
SELECT Count(field1) AS Count, Field1
FROM (select  SELECT TOP 15 * FROM TABLE)
GROUP BY field1
0
 
RamanhpCommented:
oops,

here's the soln.


SELECT Count(field1) AS Count, Field1
FROM (SELECT TOP 15 * FROM TABLE)
GROUP BY field1
0
 
hidrauAuthor Commented:
I am having this error
Incorrect syntax near the keyword 'GROUP'.
0
 
RamanhpCommented:
its working fine at my end



 select count(field_name), field_name from (SELECT TOP 15 * FROM tablename) group by field_name


in the above query, just replace the
field_name
that i suppose it
ItemType
in your table

and update the tableaname to the name of your table

0
 
hidrauAuthor Commented:
Perfect this solution :))

Thanks very much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.