Ignore the first match

I am trying to create a MS access SQL statement that compares values from the same table that ignores the first row returned.

For example for each order there may be a query call raised. The first query call is acceptable so I want to ignore that. How can I do this using SQL.

If I was using Crystal I would probably use the PREVIOUS statement to evaluate this. Can I do anything similar in SQL?

The example date I am getting is:

Incident_Number      Call_Date      Order No
CALL1748900      01-Mar-11      ORD7444
CALL2166899      08-Jul-11      ORD7444
CALL1931108      26-Apr-11      ORD7575
CALL1934264      27-Apr-11      ORD7575
CALL1971397      10-May-11      ORD7685
CALL2080797      15-Jun-11      ORD7685
CALL2080905      15-Jun-11      ORD7685
CALL2089690      17-Jun-11      ORD7685
CALL2089769      17-Jun-11      ORD7685
CALL1676199      07-Feb-11      ORD8344
CALL1678344      08-Feb-11      ORD8344
CALL1696802      14-Feb-11      ORD8344
CALL1698086      14-Feb-11      ORD8344
CALL1747866      01-Mar-11      ORD8344

So for the data shown I am only interested in CALL2166899 and as such only want this data returned by my SQL statement.
Who is Participating?
peter57rConnect With a Mentor Commented:
Couldn't say for sure but I expect so.
However, testing both  is the best bet if performance matters..
What is determining the sequence of the records?

So you are saying out of all the records you listed you only want to get the second one?
You will probbaly need a Top 1 query but the sequencing field is crucial.
Dale FyeCommented:
I agree with Peter, although I'm still trying to figure out what makes Call 2166899 for ORD7444 any different than Call 1934264 for ORD7575.

I can sort of get that where there are more than 2 records for a particular Order#, you don't want to include any of those, but don't get how you would determine to only return the 2166899 record?
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

JayceWAuthor Commented:
Hi Peter57,

The reason for the requirement is to perform counts. So from the example set of data I would want to count 1 query for order ORD7444, 1 for ORD7575, 4 for ORD7685 and 4 for ORD8344. Also I may want to cound the number of queries for a given date. for the 15-Jun-11 I would count 1 but for the 17-JUN-11 I would count 2
Given your set of data do you want to see any after the first one or just the last one?

Sorry to be so blunt but you have to decide what you are trying to do.
You are pushing out 'maybe's' and that gets nobody anywhere.

You will need different queries to count things in different ways.
JayceWAuthor Commented:
OK mlmcc sorry about that.


Not pushing out maybe's, just trying to populate a table with relationships between two tables, excluding the first relationship found. In Crystal Reports there are "PREVIOUS" and "NEXT" commands that would enable this but I was hoping not to have to use another tool on top. Do you not think that this is possible?
Are you trying to add records to a new table or are you trying to create a report showing the records that have multiple calls?

JayceWAuthor Commented:
step one is that I want to create a table that only contains the duplicate call records that I am interested in. Hence my requirement to ignore the first relationship found.

peter57rConnect With a Mentor Commented:
Starting with the sample data you posted originally, please indicate what you expect your result to be.
And describe what rule(s) you used to get your result.
JayceWAuthor Commented:
Hi Peter57r,

Thanks for your continued involvement.

For the sample data shown I would want to import all call per order number, apart from the one with the lowest call date into a table. So for that data I would have a table populated as below.

Incident_Number      Call_Date      Order No
CALL2166899      08-Jul-11      ORD7444
CALL1934264      27-Apr-11      ORD7575
CALL2080797      15-Jun-11      ORD7685
CALL2080905      15-Jun-11      ORD7685
CALL2089690      17-Jun-11      ORD7685
CALL2089769      17-Jun-11      ORD7685
CALL1678344      08-Feb-11      ORD8344
CALL1696802      14-Feb-11      ORD8344
CALL1698086      14-Feb-11      ORD8344
CALL1747866      01-Mar-11      ORD8344

peter57rConnect With a Mentor Commented:
Is the first record for each OrderNo ALWAYS the only one for that date (for that order) or can there be more than one record with the same earliest date for the same order?
JayceWAuthor Commented:
Yes the first record is the only one for that date
peter57rConnect With a Mentor Commented:
Try this query...

SELECT T.[Incident_number], T.[Order No], T.[Call_Date]
(Select [Order No], min([Call_Date]) as mdate from orders
group by [Order No])  AS q
ON (T.[Call_Date] = q.mdate) AND (T.[Order No] = q.[Order No])
WHERE (((q.[Order No]) Is Null))
ORDER BY T.[Order No], T.[Call_Date];
JayceWAuthor Commented:
Thanks Peter57r

When this came through I had already started to execute a similar NOT IN SQL statement.

Would you think that your Left join would be more efficient?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.