 |
BorlandTalk.com
Borland discussion newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
Brian Wheatley
Guest
|
 Posted: Fri Jul 02, 2004 6:13 pm Post subject: SQL Question |
 |
|
Hello,
I have an application that creates records "tickets" into a table. At one
point in my program, I need to be able to go out and retrieve the most
recent ticket entered for a particular customer or a particular job.
I have SQL code similar to this:
SELECT *
FROM
"Tickets" AS TxDB
WHERE
TxDB.[Void] = False
AND
TxDB.[Job] = :JobNo
ORDER BY
TxDB.[Ticket]
In my Delphi code I would assign the job number to the JobNo parameter, open
the query, then call the Last method to get to the last ticket for this job.
My question is this: Is there any way in SQL to select only the most recent
ticket for this job, or is this the only way?
At 500-600 tickets per day the result sets can be rather large., which takes
more time to retrieve the records and more memory to store the results.
We're currently using the BDE, but looking to migrate to MS SQL Server.
Thanks in advance,
Brian Wheatley
|
|
| Back to top |
|
 |
Del Murray
Guest
|
| Posted: Fri Jul 02, 2004 6:41 pm Post subject: Re: SQL Question |
|
|
if job numbers are assigned sequentially by the programs , not by the
customers so that there are duplicates and random sequences, then you might
look at the "top" statement in sql books on line.
|
|
| Back to top |
|
|
Iman L Crawford
Guest
|
| Posted: Fri Jul 02, 2004 7:25 pm Post subject: Re: SQL Question |
|
|
"Brian Wheatley" <brianpw (AT) bscales (DOT) com> wrote in
news:40e5a5d6$1 (AT) newsgroups (DOT) borland.com:
| Quote: | We're currently using the BDE, but looking to migrate to MS SQL
Server.
|
With the BDE and Paradox it would take multiple SQL statements.
The first query would find the last ticket number (I assume that's the
largest number) with the MAX keyword and the second would be a query for
that specific ticket number.
If on an SQL server you could implement a stored procedure, multiple
queries like above, or a query with nested queries.
something like
SELECT TXDB.*
FROM TICKETS TXDB,
(SELECT MAX(TICKET) MAX_TICKET
FROM TICKETS TXDB2
WHERE TXDB2.VOID = 'N'
AND TXDB2.JOB = :JOB_NO) TXMAX
WHERE TXDB.TICKET = TXMAX.MAX_TICKET
The above may need tweaking depending on SQL server you're using. It
should work with Oracle. The version I have doesn't have a boolean type
so I had to use 'N'.
Also when moving to a SQL Server I would strongly recommend all upper
case for metadata (tables, fields, index etc..)
--
Iman
|
|
| Back to top |
|
|
Wayne Niddery [TeamB]
Guest
|
| Posted: Sat Jul 03, 2004 1:06 am Post subject: Re: SQL Question |
|
|
Brian Wheatley wrote:
| Quote: |
I have an application that creates records "tickets" into a table. At
one point in my program, I need to be able to go out and retrieve the
most recent ticket entered for a particular customer or a particular
job.
I have SQL code similar to this:
SELECT *
FROM
"Tickets" AS TxDB
WHERE
TxDB.[Void] = False
AND
TxDB.[Job] = :JobNo
ORDER BY
TxDB.[Ticket]
In my Delphi code I would assign the job number to the JobNo
parameter, open the query, then call the Last method to get to the
last ticket for this job.
My question is this: Is there any way in SQL to select only the most
recent ticket for this job, or is this the only way?
At 500-600 tickets per day the result sets can be rather large.,
which takes more time to retrieve the records and more memory to
store the results.
We're currently using the BDE, but looking to migrate to MS SQL
Server.
|
First thing you can do is to add "desc" to the Order By, then you do not
need to go to the last record, your record will be first. Get what you need
from that record and close the query, the rest of the records won't be
fetched.
In MSSQL you could use a descending Order By and TOP 1.
You can also use a subquery:
SELECT *
FROM
"Tickets" AS TxDB
WHERE
TxDB.[Ticket] =
(SELECT Ticket FROM Tickets
WHERE TxDB.[Void] = False
AND TxDB.[Job] = :JobNo
)
--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
RADBooks: http://www.logicfundamentals.com/RADBooks.html
Working for yourself is great because you get to work half days, and
you can choose any twelve hours you want.
|
|
| Back to top |
|
|
Iman L Crawford
Guest
|
| Posted: Sat Jul 03, 2004 4:19 am Post subject: Re: SQL Question |
|
|
"Wayne Niddery [TeamB]" <wniddery (AT) chaffaci (DOT) on.ca> wrote in news:40e606a6$1
@newsgroups.borland.com:
| Quote: | First thing you can do is to add "desc" to the Order By, then you do not
need to go to the last record, your record will be first
|
Good call.
--
Iman
|
|
| Back to top |
|
|
Brian Wheatley
Guest
|
| Posted: Sat Jul 03, 2004 11:30 am Post subject: Re: SQL Question |
|
|
Thank you!!
Brian
"Wayne Niddery [TeamB]" <wniddery (AT) chaffaci (DOT) on.ca> wrote
| Quote: | Brian Wheatley wrote:
I have an application that creates records "tickets" into a table. At
one point in my program, I need to be able to go out and retrieve the
most recent ticket entered for a particular customer or a particular
job.
I have SQL code similar to this:
SELECT *
FROM
"Tickets" AS TxDB
WHERE
TxDB.[Void] = False
AND
TxDB.[Job] = :JobNo
ORDER BY
TxDB.[Ticket]
In my Delphi code I would assign the job number to the JobNo
parameter, open the query, then call the Last method to get to the
last ticket for this job.
My question is this: Is there any way in SQL to select only the most
recent ticket for this job, or is this the only way?
At 500-600 tickets per day the result sets can be rather large.,
which takes more time to retrieve the records and more memory to
store the results.
We're currently using the BDE, but looking to migrate to MS SQL
Server.
First thing you can do is to add "desc" to the Order By, then you do not
need to go to the last record, your record will be first. Get what you
need
from that record and close the query, the rest of the records won't be
fetched.
In MSSQL you could use a descending Order By and TOP 1.
You can also use a subquery:
SELECT *
FROM
"Tickets" AS TxDB
WHERE
TxDB.[Ticket] =
(SELECT Ticket FROM Tickets
WHERE TxDB.[Void] = False
AND TxDB.[Job] = :JobNo
)
--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
RADBooks: http://www.logicfundamentals.com/RADBooks.html
Working for yourself is great because you get to work half days, and
you can choose any twelve hours you want.
|
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
