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

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.
0
Tom F
Asked:
Tom F
  • 3
  • 2
  • 2
  • +3
1 Solution
 
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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
 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now