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: 1161
  • Last Modified:

Auto increment on query!

I want to write a query with an autoincrement value as a column. Something like this:

select
      ????? as number,
       mytable.field1
from
       mytable
where
       mytable.field3 like '%ABC%'

must return :

1   TTABC
2   JMABCX
3   YU ABC ABC
4   MNABC4
...
...
0
GROSSO
Asked:
GROSSO
1 Solution
 
dbbishopCommented:
There may be another way, but this will work:

IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp

SELECT  id = IDENTITY(int,1,1), mytable.field1
INTO #temp
FROM mytable
WHERE
       mytable.field3 like '%ABC%'

SELECT * FROM #temp
0
 
GROSSOAuthor Commented:
smart solution dbbishop...I'll just wait a bit in case someone else has another solution.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
if the table has some pkeys then you dont have to use a temp table / table variable
0
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

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