Home » SQL & PL/SQL » SQL & PL/SQL » Sorting Query (Oracle10g, Win2003)
| Sorting Query [message #360807] |
Mon, 24 November 2008 01:21  |
weekend79
Messages: 198
Registered: April 2005
Location: Islamabad
|
Senior Member |
|
|
I have a table for car services and the service of every car should be after call sold and before Warranty Expired.
Please advise how can I find violated records from following table
CREATE TABLE Tbl_services
(
car_no VARCHAR2(20),
action VARCHAR2(20),
action_date DATE
);
INSERT INTO tbl_services VALUES ('ABC-123','Car Sold',to_date('09-12-2008','dd-mm-yyyy'));
INSERT INTO tbl_services VALUES ('ABC-123','Service-1',to_date('10-12-2008','dd-mm-yyyy'));
INSERT INTO tbl_services VALUES ('ABC-123','Service-2',to_date('11-12-2008','dd-mm-yyyy'));
INSERT INTO tbl_services VALUES ('ABC-123','Service-3',to_date('12-12-2008','dd-mm-yyyy'));
INSERT INTO tbl_services VALUES ('ABC-123','Warranty Expired',to_date('13-12-2008','dd-mm-yyyy'));
INSERT INTO tbl_services VALUES ('XYZ-123','Service-0',to_date('06-12-2008','dd-mm-yyyy'));
INSERT INTO tbl_services VALUES ('XYZ-123','Car Sold',to_date('01-12-2008','dd-mm-yyyy'));
INSERT INTO tbl_services VALUES ('XYZ-123','Service-1',to_date('02-12-2008','dd-mm-yyyy'));
INSERT INTO tbl_services VALUES ('XYZ-123','Service-2',to_date('03-12-2008','dd-mm-yyyy'));
INSERT INTO tbl_services VALUES ('XYZ-123','Service-3',to_date('04-12-2008','dd-mm-yyyy'));
INSERT INTO tbl_services VALUES ('XYZ-123','Warranty Expired',to_date('15-12-2008','dd-mm-yyyy'));
INSERT INTO tbl_services VALUES ('XYZ-123','Service-4',to_date('06-12-2008','dd-mm-yyyy'));
SELECT * FROM tbl_services;
1 ABC-123 Car Sold 12/9/2008
2 ABC-123 Service-1 12/10/2008
3 ABC-123 Service-2 12/11/2008
4 ABC-123 Service-3 12/12/2008
5 ABC-123 Warranty Expired 12/13/2008
6 XYZ-123 Service-0 12/6/2008
7 XYZ-123 Car Sold 12/1/2008
8 XYZ-123 Service-1 12/2/2008
9 XYZ-123 Service-2 12/3/2008
10 XYZ-123 Service-3 12/4/2008
11 XYZ-123 Warranty Expired 12/15/2008
12 XYZ-123 Service-4 12/6/2008
service 0 & 4 are violated records for car XYZ-123 please advise how to find them in above table?
Wishes
|
|
|
|
| Re: Sorting Query [message #360817 is a reply to message #360807] |
Mon, 24 November 2008 01:34  |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
| Quote: | service 0 & 4 are violated records for car XYZ-123
|
Why?
December, 6th (Service-0) is after December, 1st (Car Sold).
December, 6th (Service-4) is before December, 15th (Warranty Expired).
As you did not post any other column, which would store order, the only relevant order is the one by ACTION_DATE.
You may restrict those rows from inserting into the table in the order you show, but after their creation you cannot find them (you may use some techniques, but their result is not guaranteed).
|
|
|
|
| Re: Sorting Query [message #360819 is a reply to message #360807] |
Mon, 24 November 2008 01:35 |
joicejohn
Messages: 327
Registered: March 2008
Location: India
|
Senior Member |
|
|
| weekend79 wrote on Mon, 24 November 2008 12:51 |
the service of every car should be after call sold and before Warranty Expired.
6 XYZ-123 Service-0 12/6/2008
7 XYZ-123 Car Sold 12/1/2008
8 XYZ-123 Service-1 12/2/2008
9 XYZ-123 Service-2 12/3/2008
10 XYZ-123 Service-3 12/4/2008
11 XYZ-123 Warranty Expired 12/15/2008
12 XYZ-123 Service-4 12/6/2008
[/code]
service 0 & 4 are violated records for car XYZ-123 please advise how to find them in above table?
|
As I can see the car "XYZ-123" was sold on 1st December 2008 and its warranty expired on 15 December 2008. So the Service-0 and Service-4 happened for that particular car in between those days. So the data is valid as per your requirement. Are you worried about the order in which they were entered?
Regards,
Jo
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Dec 14 01:56:48 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
