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.
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.
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?
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?
ASKER
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
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?
mlmcc
mlmcc
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.
You are pushing out 'maybe's' and that gets nobody anywhere.
You will need different queries to count things in different ways.
ASKER
OK mlmcc sorry about that.
Peter57r,
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?
Peter57r,
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?
mlmcc
mlmcc
ASKER
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.
Thanks
Thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes the first record is the only one for that date
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.