Solved

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

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now