Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 201
  • Last Modified:

how to create a column i = i +1 type in a query sql

Hello every body :D.

I need to create a query like this...:

Select MyDate, i=i +1 as [Counter] from Table

and the result like this

MyDate          Counter
12/15/2008         1
12/16/2008         2
12/17/2008         3
12/18/2008         4

If the solution works in MsAccess Query much better :)
0
JPdeLeon
Asked:
JPdeLeon
1 Solution
 
Patrick MatthewsCommented:
SELECT t1.MyDate,
    (SELECT COUNT(t2.MyDate) FROM SomeTable t2 WHERE t2.MyDate <= t1.MyDate) AS Counter
FROM SomeTable t1
ORDER BY t1.MyDate

Should work in Access and SQL Server...
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
in sql 2005+:
SELECT MyDate, RowNumber() over (order by MyDate asc) Counter
  FROM Table
 ORDER BY mydate

Open in new window

0
 
jamesguCommented:
i'd prefer angellll's query

a typo, should be Row_Number()
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now