Thanks, Tim. Excellent query, and certainly one that should save some time as the table grows. I’ll give that a shot.
Matthew,
is your application populating this table or is this being populated by a different source?
Reason why I ask is… is it critical that you store all the data or do always just need to reference the last 3 entries per lineno? if its not critical, I wonder if you are better maintaining a table that has entry for distinct line no and various columns to keep track of the various stats and information you seek.
Rich,
I read the data from a table over on a Solaris 10 server. I need the most recent record to get the status of the line and some other details on the item being run. I think I’m fine now that I read the distinct line names once per day at 0100, and save those to a global array (about 30 items). These are also read at startup in the even the program is restarted,
Thus far, with a few thousand records in the table, I am getting excellent performance. I will have to let it run over time to see how well the SQL parser on the server handles the larger database.
Thanks.
What kind of database server on Solaris ?
Oracle , PostgreSQL, Sybase ?
Sybase & MS SQL still share a number of very low level behaviors that are quite similar.
Self JOINed tables are perfectly legal in SQL. A simple example is a table of employees. Some employees are managers, some not. A single SQL statement using a self-join can produce a list of employee names with their manager’s name.
I have exactly the same problem using odbc connections to a jet database. The odbc connector will only allow one recordset per database connection. I have no problem using multiple connections for nested recordsets… however it is always much faster if you can do what you want in a single SQL statement.
Try this.
(borrowing formatting from @Dave S )
SELECT color_qc.lineno,
max(color_qc.dtstart) as dts,
etos(color_qc.dtstart),
etos(color_qc.dtstop),
color_qc.spinlot,
color_qc.color,
color_qc.colornum,
etos(color_qc.pounds),
etos(color_qc.letdown),
etos(color_qc.islabelchange),
etos(color_qc.ismakeup),
etos(color_qc.isapproved),
etos(color_qc.iscos),
color_qc.stage
FROM color_qc
GROUP BY lineno
ORDER BY lineno
This should return one line for each ‘lineno’ with the data from the last (max) ‘dtstart’, sorted by ‘lineno’.
One query, let the database do the hard work.