Solved

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

Posted on 2008-10-24
3
188 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 142

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

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 …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

772 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