Solved

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

Posted on 2008-10-24
3
193 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Database Restore 2008 R2 1 27
Display field if column exists 7 35
MSSQL Convert Char to Date Time 5 13
Need a mirrored QA test site 2 12
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

726 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