Home » SQL & PL/SQL » SQL & PL/SQL » Sorting Query (Oracle10g, Win2003)
Sorting Query [message #360807] Mon, 24 November 2008 01:21 Go to next message
weekend79
Messages: 188
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 Go to previous messageGo to next message
flyboy
Messages: 1832
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 Go to previous messageGo to next message
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
Re: Sorting Query [message #360821 is a reply to message #360807] Mon, 24 November 2008 01:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What did you try so far?
You need for each car: sold date, warantly expiry date and search for services outside them.
There are several ways to do it, self join, analytical, group by...

Regards
Michel
Re: Sorting Query [message #360827 is a reply to message #360807] Mon, 24 November 2008 01:52 Go to previous messageGo to next message
weekend79
Messages: 188
Registered: April 2005
Location: Islamabad
Senior Member

Thanks for all to reply.
Yes I did mistake in original post. Please consider service 0 & 4 before and after.
Can you consider my post once again?
Re: Sorting Query [message #360831 is a reply to message #360827] Mon, 24 November 2008 01:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe with a new test case and answer to my previous post.

Regards
Michel
Re: Sorting Query [message #360844 is a reply to message #360807] Mon, 24 November 2008 02:19 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Or even you can try with Correlated Subquery .

Show us what did you try so far ..


Smile
Rajuvan.


Previous Topic: := or =
Next Topic: Index Check
Goto Forum:
  


Current Time: Sat Dec 10 15:02:58 CST 2016

Total time taken to generate the page: 0.23784 seconds