Solved

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

Posted on 2008-10-24
3
190 Views
Last Modified: 2010-03-20
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
Comment
Question by:JPdeLeon
3 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 50 total points
ID: 22794970
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22795001
in sql 2005+:
SELECT MyDate, RowNumber() over (order by MyDate asc) Counter
  FROM Table
 ORDER BY mydate

Open in new window

0
 
LVL 9

Expert Comment

by:jamesgu
ID: 22796076
i'd prefer angellll's query

a typo, should be Row_Number()
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question