SQL Query

Heres an example of what I'd like to do:

tb_customer

firstname
lastname
phone_number
zip_code


I'd like to query this table and return all rows - but together with the info from the 4 columns in the database I'd like to include a set of consecutive numbers (from any chosen starting point) in a new column. ie. 1,2,3,4,5,6 or 15,16,17,18,19,20, etc.

If at all possible I'd like to accomplish this without writing anything to the db or altering the current structure in anyway.

Id like to accomplish this all in a single SQL Statement.

Thanks in advance.
LVL 1
Tom FI.T. and Support Staff ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lluddenCommented:
I believe Oracle provides this functionality, but MS SQL server does not.  You have to either build it progmatically, or create a temp table with an Identity field with the starting value you want and insert into that table, then display the temp table.
0
duobaCommented:
you can create a temporary table by calling it #<table_name>. This creates a temporary object that is killed when the stored procedure finishes.

An example of this might be the following



SELECT IDENTITY(int, 1,1) AS ID_Num, Contacts.first_name,Contacts.last_name, Contacts.phone_number, Contacts.zip_code
INTO #Temp
FROM Contacts

select * from #Temp
0
jpkempCommented:
If you're using Oracle, try:

SELECT firstname, lastname, phone_number, zip_code, ROWNUM + <starting_point> AS "rownum"
FROM tb_customer;

Replace "<starting_point>" with your starting number minus 1. E.g. for "10, 11, 12, 13, ..." use 9.

Jeff
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

MaxSterlingCommented:
I would first create a table, Temp that has an ID int field and the fields from your org table.  Then, have a cursor populate the table:

declare @firstname varchar(100), @i int
... other variables here

declare cur_Temp cursor for

Select firstname, [other vars]
from tb_customer
order by (whatever you want)

open cur_Temp

fetch next from cur_Temp into @firstname, [other vars]

set @i = 1 (or whatever starting value you want)

while @@fetch_status <> -1
Begin
   Insert into Temp (id, firstname, ...)
   Values (@i, @firstname, ...)


set @firstname = null
[set other vars = null]

fetch next from cur_Temp into @firstname, [other vars]

End

close cur_Temp
deallocate cur_Temp


Now, you just need to do a straight select from the temp table to get your fields.  Hope that helps
0
Tom FI.T. and Support Staff ManagerAuthor Commented:
Is there anything like the ROWNUM in Oracle for SQLServer2000?  I'd really like to avoid having to write any new tables - temporary or otherwise.
0
LowfatspreadCommented:

Select firstname
,lastname
,phone_number
,zip_code
,Pos (+ any starting Number you want)
from
tb_customer a
inner join (select custid,count(*) as Pos
              from tb_customer a
             inner join tb_customer b
                on a.custid<=b.custid
              group by custid) as B
on a.custid=b.custid


but it won't perform very fast for any meaningfull number of customers....

hth


0
LowfatspreadCommented:
sorry forgot a couple of a.
...

Select firstname
,lastname
,phone_number
,zip_code
,Pos (+ any starting Number you want)
from
tb_customer a
inner join (select a.custid,count(*) as Pos
             from tb_customer a
            inner join tb_customer b
               on a.custid<=b.custid
             group by a.custid) as B
on a.custid=b.custid


but it won't perform very fast for any meaningfull number of customers....

hth


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Tom FI.T. and Support Staff ManagerAuthor Commented:
Lowfat,

Very nice - that did the trick.  

Would you mind explaining to me what was going on with the a's and b's? I'd like to learn from this!

Thanks to everyone who gave their input!
0
LowfatspreadCommented:
Select firstname
,lastname
,phone_number
,zip_code
,Pos (+ any starting Number you want)
from
tb_customer a
inner join (select a.custid,count(*) as Pos
            from tb_customer a
           inner join tb_customer b
              on a.custid<=b.custid
            group by a.custid) as B
on a.custid=b.custid


The A & B are simply alias names for the tables
and let you identify the table a column belongs to
without having to specify the full table name...

using them also allows you to join a table to itself,
which is whats basically being done here...

the
(select a.custid,count(*) as Pos
            from tb_customer a
           inner join tb_customer b
              on a.custid<=b.custid
            group by a.custid) as B

joins the customer table to itself to give a basic ranking
to each customer based on the value of the customerid
internally within the brackets the a & b identify the
two versions of the customer table....

This whole "Temporary Table"/"ResultSet"/SubSelect I've called B (sorry bad form I should have used say X & Y inside the brackets to identify the tables)
This table (B) has two columns a Custid and Pos

I then join this table B again to the customer table
 on the customerid and you've got the position indicator that you wanted....

if you need to add further selection criteria  on the
customers then you'll need to add where clauses
both at the end of the select and in the internal select which constructs the pos...


hth        
0
MaxSterlingCommented:
That's a pretty clever way of doing that Lowfatspread
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.