SQL question 
Author Message
 SQL question

I have a table with about 500.000 records.
To find one exact record I use: "SELECT x,y,z FROM t where y='XZ672'" ORDER
BY  y;

In addition I want the recordset to return one record before and one record
after the exact match.
Is this possible without returning all 500.000 records and navigating in
them?

BR Petter



Tue, 24 Aug 2004 15:54:05 GMT  
 SQL question
would you please define,
what 'one record before and one record after'
means in this context, since there is no concept of order
in a relational database (I am assuming you use a RDBMS,
since you posted an SQL statement).

And since database query languages differ, what dbms do you use?

Dieter



Quote:
> I have a table with about 500.000 records.
> To find one exact record I use: "SELECT x,y,z FROM t where y='XZ672'"
ORDER
> BY  y;

> In addition I want the recordset to return one record before and one
record
> after the exact match.
> Is this possible without returning all 500.000 records and navigating in
> them?

> BR Petter



Thu, 26 Aug 2004 10:00:45 GMT  
 SQL question
y is unique and and has no duplicates:
Query1: "select x,y,z from t order by y";
Query2: "select x,y,z from t where y=598 order by y  +????;

I want to find the row before and after the row where y=598 in query 1;
Since t is a very large table I hoped this was possible with query 2 and
something in addition (without testing all rows from query 1). I use Oracle
8i.

Petter


Quote:
> would you please define,
> what 'one record before and one record after'
> means in this context, since there is no concept of order
> in a relational database (I am assuming you use a RDBMS,
> since you posted an SQL statement).

> And since database query languages differ, what dbms do you use?

> Dieter



> > I have a table with about 500.000 records.
> > To find one exact record I use: "SELECT x,y,z FROM t where y='XZ672'"
> ORDER
> > BY  y;

> > In addition I want the recordset to return one record before and one
> record
> > after the exact match.
> > Is this possible without returning all 500.000 records and navigating in
> > them?

> > BR Petter



Fri, 27 Aug 2004 17:44:07 GMT  
 SQL question
How about:

select * from (
select * from t where y < 598 order by y desc)
where rownum =1
union all
select * from t where y = 598
union all
select * from (
select * from t where y > 598 order by y asc)
where rownum = 1

or

select * from t
where y between
(select max(y) from t where y < 598)
and
(select min(y) from t where y > 598)

order by y

you should use a bind variable for 598

HTH Dieter



Quote:
> y is unique and and has no duplicates:
> Query1: "select x,y,z from t order by y";
> Query2: "select x,y,z from t where y=598 order by y  +????;

> I want to find the row before and after the row where y=598 in query 1;
> Since t is a very large table I hoped this was possible with query 2 and
> something in addition (without testing all rows from query 1). I use
Oracle
> 8i.

> Petter



> > would you please define,
> > what 'one record before and one record after'
> > means in this context, since there is no concept of order
> > in a relational database (I am assuming you use a RDBMS,
> > since you posted an SQL statement).

> > And since database query languages differ, what dbms do you use?

> > Dieter



> > > I have a table with about 500.000 records.
> > > To find one exact record I use: "SELECT x,y,z FROM t where y='XZ672'"
> > ORDER
> > > BY  y;

> > > In addition I want the recordset to return one record before and one
> > record
> > > after the exact match.
> > > Is this possible without returning all 500.000 records and navigating
in
> > > them?

> > > BR Petter



Sun, 29 Aug 2004 04:10:00 GMT  
 SQL question
Thank you!


Quote:
> How about:

> select * from (
> select * from t where y < 598 order by y desc)
> where rownum =1
> union all
> select * from t where y = 598
> union all
> select * from (
> select * from t where y > 598 order by y asc)
> where rownum = 1

> or

> select * from t
> where y between
> (select max(y) from t where y < 598)
> and
> (select min(y) from t where y > 598)

> order by y

> you should use a bind variable for 598

> HTH Dieter



> > y is unique and and has no duplicates:
> > Query1: "select x,y,z from t order by y";
> > Query2: "select x,y,z from t where y=598 order by y  +????;

> > I want to find the row before and after the row where y=598 in query 1;
> > Since t is a very large table I hoped this was possible with query 2 and
> > something in addition (without testing all rows from query 1). I use
> Oracle
> > 8i.

> > Petter



> > > would you please define,
> > > what 'one record before and one record after'
> > > means in this context, since there is no concept of order
> > > in a relational database (I am assuming you use a RDBMS,
> > > since you posted an SQL statement).

> > > And since database query languages differ, what dbms do you use?

> > > Dieter



> > > > I have a table with about 500.000 records.
> > > > To find one exact record I use: "SELECT x,y,z FROM t where
y='XZ672'"
> > > ORDER
> > > > BY  y;

> > > > In addition I want the recordset to return one record before and one
> > > record
> > > > after the exact match.
> > > > Is this possible without returning all 500.000 records and
navigating
> in
> > > > them?

> > > > BR Petter



Sun, 29 Aug 2004 04:46:56 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. SQL Question

2. C/SQL question

3. mini SQL question - how do i compile it???

4. Embedded SQL question

5. C, Embedded SQL Question

6. SQL question

7. SQL question - DELETE, INSERT queries

8. A little SQL question please

9. SQL Question

10. ADO SQL question

11. Very basic SQL question!

12. SQL Question!

 

 
Powered by phpBB® Forum Software