incremented count in a sql statement

[timscronin]

How can I add an incremented counter to a select statement.
Such as select last_name,date,Count? from table, so I would
get

miles,1/1/2003,1
long,1/3/2003,2
smith,1/3/2003,3

 

[SimonSellick]

I'll stick my neck out and say that I don't think it's possible in SQL Server - but it would be nice to be proved wrong! Oracle adds a pseudocolumn 'rownum' to the SELECT statement list which is designed for this purpose.

 

[SemperFiDownUnda]

Ok here is the deal. You can't do it in a normal query but you can do it via a small piece of code. It would be nice if MS put in the psuedo column "RowNum" but until then....

Basic premis is to create temp table. This table has 2 columns...basically this

CREATE TABLE #tblRowNum (
  ColID INT,
  RowNum INT IDENTITY (1, 1) NOT NULL
)

Now change the ColID data type to what ever the PK of your table you are working with is.

Now take your normal query like

SELECT DISTINCT C.LastName, C.ReviewDate
  FROM Customers C
 INNER JOIN InvoiceHeader IH
    ON C.CustID = IH.CustID
 WHERE IH.InvoiceDate BETWEEN '1 Dec 20003' AND '31 Dec 2003'
...

and it gets broken into two parts.
First you want to select those matching records into the temp table with this statement

INSERT INTO #tblRowNum
       ( ColID )
SELECT DISTINCT C.CustID 
  FROM Customers C
 INNER JOIN InvoiceHeader IH
    ON C.CustID = IH.CustID
 WHERE IH.InvoiceDate BETWEEN '1 Dec 20003' AND '31 Dec

Now you have a temp table of the matching records with a RowNum made for you.

Next just join the 2 tables but drop the WHERE clause like this

SELECT C.LastName, C.ReviewDate, RowNum
  FROM Customers C
 INNER JOIN #tblRowNum RN
    ON C.CustID = RN.ColID
 ORDER BY RN.ColID

This code in a StoredProcedure is great. You can even make stored procedures that only give you rows 11-20 by having passing those variables and putting in a WHERE clause on your final select. This is great when you want to do paging on the web. Only have the SP send back the rows that are relavant to the current page.

Hope my trick works for you.

 

[Gauss]

What about:

Select last_name,date, IDENTITY(int, 1,1) AS Count
into #t from table1
-- ("select... into..." is necessary)
select * from #t


Gauss