Getting the first row from a select 
Author Message
 Getting the first row from a select

Maybe I'm missing something obvious....

I'm trying to write a VB program that will get the first record from a query
something like:

select * from COMPANY where COMPANY_NAME >= 'whatever was entered';

The user will enter part of a name, and the VB program should retrieve the
first record >= the string entered. It is *not* a requirement that the
retrieved record be LIKE 'whatever%' (I'm trying to make this
general-purpose).

Of course, the COMPANY file may be exceptionally huge, so I don't want to
just download the entire set of records that matches the above inquiry.
Intuitively, I would have liked to be able to say:

select * from COMPANY where COMPANY_NAME >= 'whatever' and ROWNUM = 1 order
by COMPANY_NAME;

but it turns out this doesn't work - ROWNUM is assigned before the sort is
done.

I'm fairly sure I'm not the first person who's run into this :-) but I'm not
conversant enough with SQL yet to know how to do it. I have some vague idea
of making it a compound statement using MAX() or MIN() to pull out the exact
ID in the inner statement and using that to pull up an exact match in the
outer statement, but my head starts to hurt when I try to formulate it.

Any advice or suggestions?



Mon, 19 Mar 2001 03:00:00 GMT  
 Getting the first row from a select
Use the group by feature in you query and the select first for the field
that you are checking... (Correct me if im wrong)
Quote:

> Maybe I'm missing something obvious....

> I'm trying to write a VB program that will get the first record from a query
> something like:

> select * from COMPANY where COMPANY_NAME >= 'whatever was entered';

> The user will enter part of a name, and the VB program should retrieve the
> first record >= the string entered. It is *not* a requirement that the
> retrieved record be LIKE 'whatever%' (I'm trying to make this
> general-purpose).

> Of course, the COMPANY file may be exceptionally huge, so I don't want to
> just download the entire set of records that matches the above inquiry.
> Intuitively, I would have liked to be able to say:

> select * from COMPANY where COMPANY_NAME >= 'whatever' and ROWNUM = 1 order
> by COMPANY_NAME;

> but it turns out this doesn't work - ROWNUM is assigned before the sort is
> done.

> I'm fairly sure I'm not the first person who's run into this :-) but I'm not
> conversant enough with SQL yet to know how to do it. I have some vague idea
> of making it a compound statement using MAX() or MIN() to pull out the exact
> ID in the inner statement and using that to pull up an exact match in the
> outer statement, but my head starts to hurt when I try to formulate it.

> Any advice or suggestions?



Tue, 20 Mar 2001 03:00:00 GMT  
 Getting the first row from a select
A solution that is used is to add a column, e.g. ID number(10), and create a
sequence for that column (ID_SEQ) which is incremented by one each time a row is
inserted. Now you can retrieve easily the first row inserted.
But remember that the column can be looped, thus when e.g. ID = 9999999999
exists and also ID = 1 exists then of course 9999999999 is the oldest record.
Oracle thus not insert records in order of insertion, this has to do with
tablespace use etc...

Erwin



Tue, 20 Mar 2001 03:00:00 GMT  
 Getting the first row from a select

Quote:

> Maybe I'm missing something obvious....

 <SNIP>
> Intuitively, I would have liked to be able to say:

> select * from COMPANY where COMPANY_NAME >= 'whatever' and ROWNUM = 1 order
> by COMPANY_NAME;

> but it turns out this doesn't work - ROWNUM is assigned before the sort is
> done.

 <SNIP>
> Any advice or suggestions?

This seems to work for me! Try It!

SELECT *
FROM   COMPANY
WHERE  COMPANY_NAME >= 'whatever'
AND    ROWNUM < 2
ORDER BY COMPANY_NAME
by COMPANY_NAME;

Bob Anderson



Tue, 20 Mar 2001 03:00:00 GMT  
 Getting the first row from a select

Quote:


>> Maybe I'm missing something obvious....

> <SNIP>
>> Intuitively, I would have liked to be able to say:

>> select * from COMPANY where COMPANY_NAME >= 'whatever' and ROWNUM = 1
order
>> by COMPANY_NAME;

>> but it turns out this doesn't work - ROWNUM is assigned before the sort
is
>> done.

> <SNIP>
>> Any advice or suggestions?

>This seems to work for me! Try It!

>SELECT *
>FROM   COMPANY
>WHERE  COMPANY_NAME >= 'whatever'
>AND    ROWNUM < 2
>ORDER BY COMPANY_NAME
>by COMPANY_NAME;

This may work in some DBMS's - but not in Oracle. The ROWNUM is assignd to
the selected rows, THEN the selected rows are sorted. You'll get a row that
is >= 'whatever', but not necessarily the lowest-valued one that fits.

I girded my loins (what the hell does that mean anyway?) and did some
playing yesterday after posting my original message, and I found that this
works:

select * from COMPANY where COMPANY_NAME = (select min(COMPANY_NAME) from
COMPANY where COMPANY_NAME >= 'whatever');

I don't know how portable it is, but it works on Oracle. I also have no idea
how much work the server has to do to come up with this, but the important
thing from my point of view is that only one row gets sent across the
network.



Tue, 20 Mar 2001 03:00:00 GMT  
 Getting the first row from a select
the SELECT MIN will involve a fulltable or index scan !
Regards.

Quote:



>>> Maybe I'm missing something obvious....

>> <SNIP>
>>> Intuitively, I would have liked to be able to say:

>>> select * from COMPANY where COMPANY_NAME >= 'whatever' and ROWNUM = 1
>order
>>> by COMPANY_NAME;

>>> but it turns out this doesn't work - ROWNUM is assigned before the sort
>is
>>> done.

>> <SNIP>
>>> Any advice or suggestions?

>>This seems to work for me! Try It!

>>SELECT *
>>FROM   COMPANY
>>WHERE  COMPANY_NAME >= 'whatever'
>>AND    ROWNUM < 2
>>ORDER BY COMPANY_NAME
>>by COMPANY_NAME;

>This may work in some DBMS's - but not in Oracle. The ROWNUM is assignd to
>the selected rows, THEN the selected rows are sorted. You'll get a row that
>is >= 'whatever', but not necessarily the lowest-valued one that fits.

>I girded my loins (what the hell does that mean anyway?) and did some
>playing yesterday after posting my original message, and I found that this
>works:

>select * from COMPANY where COMPANY_NAME = (select min(COMPANY_NAME) from
>COMPANY where COMPANY_NAME >= 'whatever');

>I don't know how portable it is, but it works on Oracle. I also have no
idea
>how much work the server has to do to come up with this, but the important
>thing from my point of view is that only one row gets sent across the
>network.



Tue, 20 Mar 2001 03:00:00 GMT  
 Getting the first row from a select

Quote:


>> Maybe I'm missing something obvious....
>> Intuitively, I would have liked to be able to say:

>> select * from COMPANY where COMPANY_NAME >= 'whatever' and ROWNUM = 1 order
>> by COMPANY_NAME;

>> but it turns out this doesn't work - ROWNUM is assigned before the sort is
>> done.

ROWNUM is, in Oracle, effectively the physical location of the entry.
Kind of like your telephone number; just because we just asked for the
"B's" doesn't mean that the phone number gets reset to 1...

Quote:
>SELECT *
>FROM   COMPANY
>WHERE  COMPANY_NAME >= 'whatever'
>AND    ROWNUM < 2
>ORDER BY COMPANY_NAME
>by COMPANY_NAME;

This is still basing the query on the physical row number, which is, if
memory serves, assigned at the time the record is inserted.

As such, the physical row number doesn't forcibly bear any relationship
to anything semantically useful.  The "physical" key is useful if you
want to do a query in which you update records in ways that might alter
any data-oriented key, thus invalidating a query ordered by that key.

My Oracle reference guide is 8 miles away, so I can't verify what syntax
should be used.  Possibly something like:

select * from table
where company_name >= 'whatever'
order by company_name
up to 1 rows.

--
"What you end up with, after running an operating system concept
through these many marketing coffee filters, is something not unlike
plain hot water."   -- Matt Welsh



Wed, 21 Mar 2001 03:00:00 GMT  
 Getting the first row from a select

Quote:
> ROWNUM is, in Oracle, effectively the physical location of the entry.
> Kind of like your telephone number; just because we just asked for the
> "B's" doesn't mean that the phone number gets reset to 1...

I think you are referring to ROWID, which in Oracle7 was of the form
"block.slot.file," and in Oracle8 is an opaque identifier that can be
decoded to provide the block, slot, and file of a row.

ROWID isn't really "assigned" at any time, but is rather more like an
address than the phone number in your analogy. It just means "this is
where the row is actually stored in the database." Indexes do physically
store rowids for purposes of referencing a table row. Can anyone tell the
other type of segment that uses rowid references?

ROWNUM, on the other hand is just the counter of rows returned by a query.

--
Jeremiah Wilton      http://www.wolfenet.com/~jeremiah



Wed, 21 Mar 2001 03:00:00 GMT  
 Getting the first row from a select
Hi there,

I may have missed something the others answers, but the fastest way I know
to retrieve a record (espcially the first one) is using an index.

If you set an index on the COMPANY Table (let say Company), the following
code should do the trick

'Open the table
set rec=db.openrecordset("Company",dbopentable)

'Tell the engine you seek using that index
rec.index="Company"

'Lookup the item
rec.seek ">=", WhateverWasEntered

'check you found one
if not rec.nomatch then
    'BINGO, YOU HAVE THE FIRST ONE AND YOU DID NOT BROWSE THE HUGE TABLE...
endif

Hope it helped.

Stephane

Quote:

>Maybe I'm missing something obvious....

>I'm trying to write a VB program that will get the first record from a
query
>something like:

>select * from COMPANY where COMPANY_NAME >= 'whatever was entered';

>The user will enter part of a name, and the VB program should retrieve the
>first record >= the string entered. It is *not* a requirement that the
>retrieved record be LIKE 'whatever%' (I'm trying to make this
>general-purpose).

>Of course, the COMPANY file may be exceptionally huge, so I don't want to
>just download the entire set of records that matches the above inquiry.
>Intuitively, I would have liked to be able to say:

>select * from COMPANY where COMPANY_NAME >= 'whatever' and ROWNUM = 1 order
>by COMPANY_NAME;

>but it turns out this doesn't work - ROWNUM is assigned before the sort is
>done.

>I'm fairly sure I'm not the first person who's run into this :-) but I'm
not
>conversant enough with SQL yet to know how to do it. I have some vague idea
>of making it a compound statement using MAX() or MIN() to pull out the
exact
>ID in the inner statement and using that to pull up an exact match in the
>outer statement, but my head starts to hurt when I try to formulate it.

>Any advice or suggestions?



Fri, 23 Mar 2001 03:00:00 GMT  
 Getting the first row from a select

Quote:
> I think you are referring to ROWID, which in Oracle7 was of the form
> "block.slot.file," and in Oracle8 is an opaque identifier that can be
> decoded to provide the block, slot, and file of a row.

Opaque identifier?  They just went from representing it in base 16
(hexidecimal) to base 52 (or some numbr around there).  Doesn't everyone
know base 52?  <g>
--
Best!

Randall

[Just my Dog and I at the Edge of the Universe]
 -----------------------------------------
(Remove "-zzz" from address when replying.)
 ----------------------------------------



Sat, 24 Mar 2001 03:00:00 GMT  
 Getting the first row from a select

(if that email address didn't require changing)

Quote:

>> I think you are referring to ROWID, which in Oracle7 was of the form
>> "block.slot.file," and in Oracle8 is an opaque identifier that can be
>> decoded to provide the block, slot, and file of a row.

>Opaque identifier?  They just went from representing it in base 16
>(hexidecimal) to base 52 (or some numbr around there).  Doesn't everyone
>know base 52?  <g>

well, its base64 encoded, not 52 and its very easy to decode as follows:

SQL> select rowid,
  2             dbms_rowid.rowid_relative_fno( rowid ) "Relative File#",
  3             dbms_rowid.rowid_block_number( rowid ) "Block#",
  4             dbms_rowid.rowid_row_number( rowid ) "Row#"
  5  from dual;

ROWID              Relative File#     Block#       Row#
------------------ -------------- ---------- ----------
AAAACsAABAAAAGiAAA              1        418          0

so, in some ways, due to the dbms_rowid package, its easy in O8 to get this info
then in O7....

Thomas Kyte

Oracle Government
Herndon VA

--
http://govt.us.oracle.com/    -- downloadable utilities

----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you,
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.



Sun, 25 Mar 2001 03:00:00 GMT  
 Getting the first row from a select

Quote:
>'Open the table
>set rec=db.openrecordset("Company",dbopentable)

>'Tell the engine you seek using that index
>rec.index="Company"

>'Lookup the item
>rec.seek ">=", WhateverWasEntered

>'check you found one
>if not rec.nomatch then
>    'BINGO, YOU HAVE THE FIRST ONE AND YOU DID NOT BROWSE THE HUGE TABLE...
>endif

I am not a VB DAO expert but...I thought the DAO .seek function performed a
full table search.  I believe this would involving reading all of the
records into the client to scan for the 'WhateverWasEntered' this could
potentially take a long very long time.

Cheers...David



Sun, 25 Mar 2001 03:00:00 GMT  
 Getting the first row from a select

Quote:
>Maybe I'm missing something obvious....

>I'm trying to write a VB program that will get the first record from a
query
>something like:

>select * from COMPANY where COMPANY_NAME >= 'whatever was entered';

Using the dbOpenForwardOnly type of recordset may eliminate the cost of
doing the simple query and only using the first retrieved column.

Under certain conditions you can get the first row by adding the clause
ROWNUM=1 to the query.

Cheers...David



Sun, 25 Mar 2001 03:00:00 GMT  
 Getting the first row from a select

Quote:
>>'check you found one
>>if not rec.nomatch then
>>    'BINGO, YOU HAVE THE FIRST ONE AND YOU DID NOT BROWSE THE HUGE
TABLE...
>>endif

>I am not a VB DAO expert but...I thought the DAO .seek function performed a
>full table search.  I believe this would involving reading all of the
>records into the client to scan for the 'WhateverWasEntered' this could
>potentially take a long very long time.

It does. And it does. My original post specified that the file is BIG and
this solution would not be acceptable. I have to be able to filter the
dynaset down to 1 record *before* it gets transmitted across the network.


Sun, 25 Mar 2001 03:00:00 GMT  
 Getting the first row from a select
Hi,

No, the DAO seek, is the fastest access as it uses indexes and not ful tabel
scan. That is why you may have to specify which index you are using. When
you issue the seek, it should browse the index using a search algorithm (as
indexes are sorted) and return you only the first row.

Anyway, if you have a big table, it is only a matter of writting 3 lines to
give it a try. Let me know what it gives for the results.

Stephane


Quote:
>>'Open the table
>>set rec=db.openrecordset("Company",dbopentable)

>>'Tell the engine you seek using that index
>>rec.index="Company"

>>'Lookup the item
>>rec.seek ">=", WhateverWasEntered

>>'check you found one
>>if not rec.nomatch then
>>    'BINGO, YOU HAVE THE FIRST ONE AND YOU DID NOT BROWSE THE HUGE
TABLE...
>>endif

>I am not a VB DAO expert but...I thought the DAO .seek function performed a
>full table search.  I believe this would involving reading all of the
>records into the client to scan for the 'WhateverWasEntered' this could
>potentially take a long very long time.

>Cheers...David



Sun, 25 Mar 2001 03:00:00 GMT  
 
 [ 16 post ]  Go to page: [1] [2]

 Relevant Pages 

1. Getting row number variable from a selected row in Excel

2. select only first rows

3. Select first rows.

4. Select all except the first row

5. Updating the R first rows of a SELECT containing an ORDER BY statement

6. ListView selects first row

7. Full row selection in MSHflexgrid does not select first column

8. selecting a record in a DBGrid and always getting the first one

9. Visdata app corrupts first col of first row in table

10. Getting Selected row from a datagrid

11. Getting a Selected Row of an MSFlexGrid

12. Getting a Selected Row of an MSFlexGrid

 

 
Powered by phpBB® Forum Software