I'm having trouble putting together a SQL statement. I know this is a smart group of guys, and I was hoping some of you have some SQL experience and might be able to help me out.

Using PHP and ODBC/Access, I'm trying to select a subset of records (the first x records) instead of pulling back the whole record set. In MySQL, I can use the LIMIT keyword, but in ANSI SQL (and apparently Access) that keyword doesn't exsist.

Everybody suggests the same thing..using TOP x, but that doesn't work for me.
SELECT TOP 10 keyfield, nonkeyfield FROM tablename ORDER BY keyfield returns the first 10 records as expected, but...
SELECT TOP 10 keyfield, nonkeyfield FROM tablename ORDER BY nonkeyfield returns more than 10 records when the values in nonkeyfield are not unique.

So, I need to do the equiv of:
SELECT keyfield, nonkeyfield FROM tablename ORDER BY nonkeyfield LIMIT 0,10
which works in MySQL, but not with any other SQL DBs.

Anybody have any suggestions?

- Chris
32GB MK2 090000664 Smoke
Queue # 2 (who the heck was 1?)

Isn't there a 'UNIQUE' keyword that you might be able to use? Please ignore me if I'm talking complete bull. I don't have that much SQL experience .

Marcus (beaker)
32 gig (various colours)

UNIQUE doesn't help me here. That will pull out only records that are unique across all fields, but in this case, ALL the records in the table are unique when including the keyfield. You would use UNIQUE if you wanted to pull back just one of multiple identical rows. I don't have any identical rows in my table.

- Chris
32GB MK2 090000664 Smoke
Queue # 2 (who the heck was 1?)

I am not familiar with top (does not seem to exist in oracle which I use), but you should be able to apply unique to a single column. I.e.

select unique(columnA), columnB from tableC where ...

since I don't seem to have a top function I have no idea if that helps though :-)

-Mike

That method doesn't work for me. Returns an "undefined function" error. Even if it did work, though, I'm not sure it would help me any...that wouldn't do what I need it to.

- Chris
32GB MK2 090000664 Smoke
Queue # 2 (who the heck was 1?)

Officially, pure SQL is a set-based language i.e. queries return a set of results and the overall number of records is arbitrary. This isn't too practical for what you want to do, so most database vendors extend SQL with their own mechanism for limiting queries to 'n' records e.g. Sybase allows you to "set @@rowcount 10" for example. The problem with this is that you need to ensure you have the records ordered correctly, as you'll just return the first 10 records that match.

The correct SQL technique for this type of problem is to use a cursor. You declare a cursor for a given SQL statement, then open it (which executes the query), then you can fetch individual records using procedural code and close it when you are done. You should be able to break out of the loop when you get to 10 records. I'm not familiar with Access syntax, but there should be enough information in the help to get you going...

Email me directly if you're stuck and I'll see if I can find you an example of a cursor, but I think you'll probably find stuff on the internet faster than I can dig out a book!

Hope that helps!

Robin
(rmail: [email protected])

Yep. A big part of the answer to this is "it depends" on the database.

But one reasonable (arguably) way to do top 10 is to select into a temp table with an autonumbering column. Then you follow it with a second select for column<=10. You can incorporate this functionality into a proc, and it's in theory ugly but in practice quite fast.

Calvin

To follow-up Calvin's post:

Option A:

1. Create temp table with autonumber column (either declared as such, or via a trigger that does an "update x = select max(x)")
   
2. Select into this table (ideally limiting rows processed to 10 for performance reasons)
   
3. Select * from this table

Option B:

1. create cursor for your select
   
2. loop 10 times and fetch from your cursor (either directly to the front-end or into a temp table)
   
3. (if required) select * from temp table

Option C:

1. A specific Access call to limit the number of rows such as Top or TopValues (sorry I don't know more Access)

Good luck,

Robin

Some databases support on-the-fly temp tables that self garbage collect.

If you're very clever, you can nest-select-insert-select and pull a top 10. But you have to be clever. Depending on the database, you'll get yelled at, or you'll get very fast top tens that nobody else will be able to understand because you're so clever....

Calvin

I like option B dumping straight to the front end, but I'm not familiar enough with SQL or Access to know how to use cursors. I suppose now is as good of a time as any to learn. This is something that takes place in the DB engine, right? I'd have to find how to implement this somewhere in Access documentation.

Building a temp table on the backend is a bit more convoluted than I was aiming for. I'm not too sure I'd get much more performance out of that, then my current work around. Right now I have the query bringing back all records, and then using PHP code to control what's actually printed out. Doesn't seem too slow now, but I'm worried it's not scalable to large data sets and many concurrent users sessions.

I miss MySQL.

- Chris
32GB MK2 090000664 Smoke
Queue # 2 (who the heck was 1?)

select unique(columnA), columnB from tableC where ...

Won't work. Consider

columnA  columnB  

-----------------

10       AAA

10       BBB

Which row should it return, (10, AAA) or (10, BBB)?

Besides, I think that ANSI standard is distinct, not unique.

Dragi "Bonzi" Raos
Zagreb, Croatia
Q#5196, MkII#80000376, 18GB green

You said you access your Access through ODBC, if I understood correctly. ODBC has reasonably standard cursor support, and certinly the simplest case (non-updateable non-scrollable) you have here. The question is whether PHP ODBC interface has appropriate mapping mechanisms (I know nothing about PHP).

BTW, some advanced tools like Panther (see open source version) do this automagically: you draw your form, name fields the same as database columns (there are other mapping mechanisms, of course) and emit SELECT. It will declare the cursor 'behind the scenes' and populate as much rows as there are on the form (one can do this explicitly, as well).

Dragi "Bonzi" Raos
Zagreb, Croatia
Q#5196, MkII#80000376, 18GB green

Edited by bonzi on 24/10/01 12:50 AM.

Perhaps I am missing something, but it looks to me as if this example found on http://php.weblogs.com/odbc is exactly what you need:

// connect to a DSN "mydb" with a user and password "marin" 

$connect = odbc_connect("mydb", "marin", "marin");



// query the users table for name and surname

$query = "SELECT name, surname FROM users";



// perform the query

$result = odbc_exec($connect, $query);



// fetch the data from the database; 

// add the counter and your 10 rows only criterion

while(odbc_fetch_row($result)){

  $name = odbc_result($result, 1);

  $surname = odbc_result($result, 2);

  print("$name $surname\n");

}



// close the connection

odbc_close($connect);

?>

Dragi "Bonzi" Raos
Zagreb, Croatia
Q#5196, MkII#80000376, 18GB green

No...that example does exactly what I'm doing now. It pulls back all the records in the table that match the criteria of the SQL statement, and then only displays the one you're interested in displaying. I was seeking a way to only select a specific number of the records from the database and display all the retrieved rows, then select the next x records and display those. Unless I'm misunderstand how PHP works under the hood, this would improve performance. Instead of giving PHP the whole set of records to work with and using PHP to limit what is printed out, I wish to let the DB sort out what to give to PHP to print. DB servers are much better at that than scripting languages.

Then again, perhaps PHP's odbc_fetch_row() gets one row at a time from the DB server, instead of getting the row from a record set that odbc_exec() already pulled out (which is what I believe). I guess the real question is, what happens "under the hood" when the odbc_exec() funtion is executed.


- Chris
32GB MK2 090000664 Smoke
Queue # 2 (who the heck was 1?)

if you replace the second SQL query with one like this:

SELECT TOP 10 nonkeyfield, keyfield FROM tablename ORDER BY nonkeyfield


You will get the top 10 rows on non key field - even if keyfield is less than 10 unique values.

Now, the problem is that the column order is back to front from the earlier example as the TOP 10 keyword must precede the first row being selected or the SQL query engine chokes.
If the odd column order is not a problem, then you have a solution - two different queries.

However, if this is a problem, in that you'd like the column order to be the same for both queries then you have a problem.

One way around this is to use a "derived table" - something which I know SQL Server supports, and Access may well also.

A Derived table lets you 'create' a on-the fly table from a query and then query it with a second SQL statement, you can use this trick to run the query with the wrong column order(as above) then produce a query on this derived table to produce the right order of columns, and/or change the sort order of the columns.
An example of using a derived table using the above SQL would be:

select keyfield,nonkeyfield from
(SELECT TOP 10 nonkeyfield, keyfield FROM tablename ORDER BY nonkeyfield ) as Q1

(add optional order by statements here)


The Query in the brackets is the one I gave which gives the wrong order, you then make it a derived table by enclosing it in brackets '(' and ')' and adding the 'as Q1 ' I used the name Q1 as an arbitary name for this intermediate table, its never explicitly referenced, then prepending a 'select nonkeyfield,keyfield from' in front of the query.

Now I don't promise the Derived table idea will work on your Access/PHP system, but give it a go.
Also note that you could add a 'order by keyfield' at the end to get the table order back to keyfield order - except only the first 10 rows are ever returned even if using the keyfield order

Hope this helps.

Then again, perhaps PHP's odbc_fetch_row() gets one row at a time from the DB server, instead of getting the row from a record set that odbc_exec() already pulled out (which is what I believe). I guess the real question is, what happens "under the hood" when the odbc_exec() funtion is executed.

Exactly. I assumed that odbc_exec opens the cursor, and odbc_fetch_row, well, does exactly that, fetches the row from it. Then again, it could be as you say, that odbc_exec fetches the whole result set into some internal buffer, and odbc_fetch_row just transfers a row into your variables. Well, I will be very disappointed it the later is the case.

You could try and experiment with exclusive locks (or dig through documentation and/or source) to see which is true.

I will try and poke around docs available on the web if I remember tomorrow morning (it is 2:20 AM here).

Dragi "Bonzi" Raos
Zagreb, Croatia
Q#5196, MkII#80000376, 18GB green

P.S.
I took a brief look at PHP/ODBC docs and it looks as if I might be right, but it is not 100% clear. They say that exec is combination of prepare and execute, and those are normal names for the steps in preparing and opening a cursor using dynamic SQL. It could still be that exec and execute return the whole result set somewhere, and fetch gets rows from there, but I would be surprised.

Try an experiment: take a large table and do unsorted select on it without where part. If exec returns more or less immediatelly, I am right.

Edited by bonzi on 24/10/01 01:36 AM.

In reply to:

---

Exactly. I assumed that odbc_exec opens the cursor, and odbc_fetch_row, well, does exactly that, fetches the row from it. Then again, it could be as you say, that odbc_exec fetches the whole result set into some internal buffer, and odbc_fetch_row just transfers a row into your variables. Well, I will be very disappointed it the later is the case.

---

Well Bonzi, prepare to be disappointed.
When you run a odbc_fetch_row it will generally by default fetch a large chunk of the rowset [beyond 10 rows - normally 10-20KB of data] into internal buffers when the first fetch_row is called.

This is what we call Firehose (client) cursors - you get more rows/data returned than you generally need.

You can force the ODBC driver to create a cursor on the Database Server rather than the client to help remove this problem, but this simply pushes the memory usage etc of the database server as it has to store the query resultset until the client asks for it or closes the cursor.
If the Database server and php server are the same the effect is probably neither here nor there.

Well it works with oracle (I just tried it again to be sure). Of course as you mentioned it is only going to return one row and I am not sure how it determines which row to return. Distinct does the same thing FWIW.

-Mike

Hmm, fetching more than needed have unpleasant locking effects when updateable and/or scrollable cursors, more aggresive isolation levels and transaction control are used... Well, I suppose these Firehose cursors are only used in read-only situations like this, and they are probably more efficient on average... Then again, we are speaking about web aplications, where scrollable cursors, long transactions etc make no sense.

Thanks for clarification.

Dragi "Bonzi" Raos
Zagreb, Croatia
Q#5196, MkII#80000376, 18GB green

I don't have Oracle handy now. Please try my example (or similar) and see what it returns. I anything, the results of SQL queries should be predictable .



Dragi "Bonzi" Raos
Zagreb, Croatia
Q#5196, MkII#80000376, 18GB green

We're probably getting too far away from the original function of the app for answers that will help me (though I'm learning new stuff about SQL, and that's always good). Due to the dynamic nature of the orignal app and the way I have SQL statements being generated, I'm not sure I'm in a position to implement some of the more advanced techniques here. I might have to start over and rewrite the app keeping this limitation in mind, as I wasn't expecting this to be a problem when I started writing the app. I'm too used to having MySQL's LIMIT keyword which allows me to specify the starting row and number of rows returned, right in the SQL statement. I wonder why something that straightforward isn't built into most other DB servers (or standard SQL for that matter).

Firehose cursors...hmm..you learn something new everyday.

- Chris
32GB MK2 090000664 Smoke
Queue # 2 (who the heck was 1?)

Ahhh, you got me. I wasn't paying enough attention to the data in the table I was using and some of the pairs where not unique. Since it was returning less rows than the total number of rows for the table I was assuming that it was working. For the record...

-------------
SQL> select * from tabc;

COLUMNA COLUMNB
---------- ----------
asdf one
asdf two

SQL> select unique(columna), columnb from tabc where columna='asdf';

COLUMNA COLUMNB
---------- ----------
asdf one
asdf two

SQL>
-------------


so it is doing unique for the pair. Of course, either way it is of no help to the original poster.

-Mike

I'm too used to having MySQL's LIMIT keyword which allows me to specify the starting row and number of rows returned, right in the SQL statement. I wonder why something that straightforward isn't built into most other DB servers (or standard SQL for that matter).

Because SQL is a set-oriented language (also, tables or relations from relational model don't have inherent ordering: they are also sets of rows; keys are the means of identifying, not ordering). Now, the question might be why are we using such a data access language from procedural languages... Well, SQL was originally intended to be an end-user language, not embedded one (figure that - giving your end-users unrestricted SQL!).

Anyway, my advice is to always try and use only standard language (and not the most recent standard!). If you succumb to temptation to massively use a nice non-standard feature (like I did with Informix temporary tables, vastly more convenient and usefull than those in, say, Oracle or DB2), you will get yourself in trouble sooner or later . At least have a strategy in place for replacing those machanism with standard ones.

Finally, test the impact of those 'firehose cursors' on performance of your app. If your web server and database server are either on the same machine or on reasonably fast and uncongested LAN, I suppose they are going to be small, if not negligible.

Let us know about results!

Dragi "Bonzi" Raos
Zagreb, Croatia
Q#5196, MkII#80000376, 18GB green

If your web server and database server are either on the same machine or on reasonably fast and uncongested LAN, I suppose they are going to be small, if not negligible

It's not the same machine, no. I have it set up that way now (with firehose cursor), and I don't notice any lag at all in the app, but I don't really know how I would do a stress test against it to know how it will perform with many users.

As for keeping it standard...yeah, I hear ya. I'm building an app that must support varied DB servers, by nature...and I'm writing seperate modules for each. I know that's not keeping it simple with standard SQL, but PHP uses different functions to access different types of DB servers, so I have to write different connection modules anyway...I just figured I'd take advantages of some of the more helpful features of each DB server while I'm at it. For example, I'm using the LIMIT keyword in my MySQL connector when running queries.

- Chris
32GB MK2 090000664 Smoke
Queue # 2 (who the heck was 1?)