SQL Question

SQL Question

Post by js » Sun, 31 Dec 1899 09:00:00



I am trying to figure out how to get a result set in which the row
number is a column in the result set.  For example, if I select n rows
from a table, I need a column to contain 1 thru n.  Optimally, the
syntax needs to be ANSI standard SQL, but nearly any solution will do.
Anyone have ideas?

TIA
JS

 
 
 

SQL Question

Post by Krishnan Subramanya » Sun, 31 Dec 1899 09:00:00


hello-

you can try the following. not sure if it will work but it's definitely
worth a shot.

create a sequence (say MySeq) and add the sequence to the select statement.
for example,

select MySeq.nextval, .... from table;

the sequence must be reinitialized to 1 before any new query so it's more
like..

drop sequence MySeq;
create sequence MySeq start with 1 increment by 1;
select MySeq.nextval, .... from table;

this is a proposed solution for oracle. may/may not work with other
databases.

let me know if it works.

-- krishnan

 
 
 

SQL Question

Post by Trevor Be » Sun, 31 Dec 1899 09:00:00


On Sat, 20 Jun 1998 14:57:14 -0700 in comp.databases.ms-access, js


>I am trying to figure out how to get a result set in which the row
>number is a column in the result set.  For example, if I select n rows
>from a table, I need a column to contain 1 thru n.  Optimally, the
>syntax needs to be ANSI standard SQL, but nearly any solution will do.
>Anyone have ideas?

See my FAQ, queries section (Sequence Numbers on the fly).

                    \|||/
                    /   \
                   C o o D
-----------------ooO--u--Ooo-------------------------------
To reply by mail, replace the "nospam" in my address with "trevor",
then replace the "o" in easynot with an "e".
But please DONT cc me in on posts, I read this group as well.
MS Access FAQ now available on my site below.
http://www.trevor.easynet.co.uk/

Kenny's not dead.

 
 
 

SQL Question

Post by isg coop » Sun, 31 Dec 1899 09:00:00


For sybase, you can create a temp table with identity column which
increment as row insert into the temp table.  Finally, after insert your
result set into the temp table, you can then select * from the #temptable.

select *, id_col=identity(5)
into #new_discounts
from discounts

select *
from #new_discounts
go


> I am trying to figure out how to get a result set in which the row
> number is a column in the result set.  For example, if I select n rows
> from a table, I need a column to contain 1 thru n.  Optimally, the
> syntax needs to be ANSI standard SQL, but nearly any solution will do.
> Anyone have ideas?

> TIA
> JS

 
 
 

SQL Question

Post by isg coop » Sun, 31 Dec 1899 09:00:00


Moreover, the syntax only work on syabse but not ms-sql


> For sybase, you can create a temp table with identity column which
> increment as row insert into the temp table.  Finally, after insert your
> result set into the temp table, you can then select * from the #temptable.

> select *, id_col=identity(5)
> into #new_discounts
> from discounts

> select *
> from #new_discounts
> go


> > I am trying to figure out how to get a result set in which the row
> > number is a column in the result set.  For example, if I select n rows
> > from a table, I need a column to contain 1 thru n.  Optimally, the
> > syntax needs to be ANSI standard SQL, but nearly any solution will do.
> > Anyone have ideas?

> > TIA
> > JS

 
 
 

SQL Question

Post by Ngkc Pau » Sun, 31 Dec 1899 09:00:00


The solution you provided in ms-access.  Can I use the code in Visual
Basic?


> On Sat, 20 Jun 1998 14:57:14 -0700 in comp.databases.ms-access, js

> >I am trying to figure out how to get a result set in which the row
> >number is a column in the result set.  For example, if I select n rows
> >from a table, I need a column to contain 1 thru n.  Optimally, the
> >syntax needs to be ANSI standard SQL, but nearly any solution will do.
> >Anyone have ideas?

> See my FAQ, queries section (Sequence Numbers on the fly).

>                     \|||/
>                     /   \
>                    C o o D
> -----------------ooO--u--Ooo-------------------------------
> To reply by mail, replace the "nospam" in my address with "trevor",
> then replace the "o" in easynot with an "e".
> But please DONT cc me in on posts, I read this group as well.
> MS Access FAQ now available on my site below.
> http://www.trevor.easynet.co.uk/

> Kenny's not dead.

 
 
 

SQL Question

Post by isg coop » Sun, 31 Dec 1899 09:00:00


For oracle, it is easy.  you can say

select rownum, a.*
from table1 a
where xxx

remember use tablex.* rather than just *


> hello-

> you can try the following. not sure if it will work but it's definitely
> worth a shot.

> create a sequence (say MySeq) and add the sequence to the select statement.
> for example,

> select MySeq.nextval, .... from table;

> the sequence must be reinitialized to 1 before any new query so it's more
> like..

> drop sequence MySeq;
> create sequence MySeq start with 1 increment by 1;
> select MySeq.nextval, .... from table;

> this is a proposed solution for oracle. may/may not work with other
> databases.

> let me know if it works.

> -- krishnan

 
 
 

SQL Question

Post by Volker B? » Sun, 31 Dec 1899 09:00:00



> I am trying to figure out how to get a result set in which the row
> number is a column in the result set.  For example, if I select n rows

> from a table, I need a column to contain 1 thru n.  Optimally, the
> syntax needs to be ANSI standard SQL, but nearly any solution will do.

> Anyone have ideas?

> TIA
> JS

Hi,if the selected rows are sorted (for example by field 'sort_field')
the following will work:
'select (select count (sort_field) +1 from table t1 where
t1.sort_field<t2.sort_field), .... from table t2 order by t2.sort_field'

Volker Boess

 
 
 

SQL Question

Post by Ngkc Pau » Sun, 31 Dec 1899 09:00:00


Some problems for your SQL.  First, it cannot run on oracle,  Seconds, if
the sort key is not unique, the result may be wrong.  Third, it may cause
performance isuue if the table is large.



> > I am trying to figure out how to get a result set in which the row
> > number is a column in the result set.  For example, if I select n rows

> > from a table, I need a column to contain 1 thru n.  Optimally, the
> > syntax needs to be ANSI standard SQL, but nearly any solution will do.

> > Anyone have ideas?

> > TIA
> > JS

> Hi,if the selected rows are sorted (for example by field 'sort_field')
> the following will work:
> 'select (select count (sort_field) +1 from table t1 where
> t1.sort_field<t2.sort_field), .... from table t2 order by t2.sort_field'

> Volker Boess

 
 
 

SQL Question

Post by Volker B? » Sun, 31 Dec 1899 09:00:00


Why does it not run on Oracle?
With the performance Problem you are right.
If you have an enumerated list, you probably have a unique sort key.
Otherwise you have to count more than one row for one position.
I used this solution to sort a field of players by their score.
If two players are at position 2 for example, the next player will
follow on position 4 (which is correct in this case).

 
 
 

SQL Question

Post by Arun Shastr » Sun, 31 Dec 1899 09:00:00


The simplest I can think of is:

create temp table  foo(seq serial, <columns>);

insert into foo(<columns>)
select <stuff>;

<columns> is everything you want in your select, without the row numbers.

Hope that helps,
Arun


> I am trying to figure out how to get a result set in which the row
> number is a column in the result set.  For example, if I select n rows
> from a table, I need a column to contain 1 thru n.  Optimally, the
> syntax needs to be ANSI standard SQL, but nearly any solution will do.
> Anyone have ideas?

> TIA
> JS

 
 
 

SQL Question

Post by Trevor Be » Sun, 31 Dec 1899 09:00:00


On Mon, 22 Jun 1998 12:42:48 +0800 in comp.databases.ms-access, Ngkc Paul


>The solution you provided in ms-access.  Can I use the code in Visual
>Basic?

AFAIK No, VB doesn't seem to have the ability to call functions from a
query, whether the function is defined in Access or in VB, if your just
opening a recordset in VB code then just implement a counter using a
variable but apart from that I don't think so.

                    \|||/
                    /   \
                   C o o D
-----------------ooO--u--Ooo-------------------------------
To reply by mail, replace the "nospam" in my address with "trevor",
then replace the "o" in easynot with an "e".
But please DONT cc me in on posts, I read this group as well.
MS Access FAQ now available on my site below.
http://www.trevor.easynet.co.uk/

Kenny's not dead.

 
 
 

SQL Question

Post by Trevor Be » Sun, 31 Dec 1899 09:00:00


On Mon, 22 Jun 1998 14:06:23 +0200 in comp.databases.ms-access, "Volker


>Why does it not run on Oracle?

because it's a toy :-)

<duck>

                    \|||/
                    /   \
                   C o o D
-----------------ooO--u--Ooo-------------------------------
To reply by mail, replace the "nospam" in my address with "trevor",
then replace the "o" in easynot with an "e".
But please DONT cc me in on posts, I read this group as well.
MS Access FAQ now available on my site below.
http://www.trevor.easynet.co.uk/

Kenny's not dead.

 
 
 

1. Access question -> SQL question

Hello,

I have a Access question that looks like this:

AckCykler Justerat: IIf((([Cykl / Fh])<4) Or (([Cykl /
Fh])>8);[Tab_QDetalj].[TotDrifttid]*8;[AckCykler])

How do I do the same question in a SQL query?

/Roberto

2. Server ???

3. Update question and SQL question

4. Monitor script

5. Simple SQL Question for the SQL Gurus

6. Mutating tables?

7. SQL question (not really sql server programming)

8. Informix Message Log

9. SQL Gurus: Time SQL question

10. SQL Server T-SQL Question

11. SQL Question: Best way to pass array data to SQL Server

12. SQL question for SQL Gurus

13. more dynamic SQL-Server SQL question