?
Solved

SQL Query

Posted on 2003-03-31
10
Medium Priority
?
515 Views
Last Modified: 2012-05-04
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
Comment
Question by:Tom F
[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
  • 2
  • 2
  • +3
10 Comments
 
LVL 18

Expert Comment

by:lludden
ID: 8242143
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
 
LVL 1

Expert Comment

by:duoba
ID: 8242148
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
 
LVL 5

Expert Comment

by:jpkemp
ID: 8242523
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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 

Expert Comment

by:MaxSterling
ID: 8243044
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
 
LVL 1

Author Comment

by:Tom F
ID: 8243058
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 8245129

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

Accepted Solution

by:
Lowfatspread earned 400 total points
ID: 8245152
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
 
LVL 1

Author Comment

by:Tom F
ID: 8250669
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 8255143
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
 

Expert Comment

by:MaxSterling
ID: 8257588
That's a pretty clever way of doing that Lowfatspread
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

801 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