Help on SQL Query

240 pts.
Tags:
SQL
SQL Query
Hi, I have a table which has customer order and date in the below format. customer order Date 1 1a 02/10/2008 19:48 1 1b 02/10/2008 18:48 1 1c 02/10/2008 17:48 1 1d 03/10/2008 19:48 2 2a 01/11/2008 01:30 2 2b 02/11/2008 01:30 2 2c 03/11/2008 03:45 2 2d 03/11/2008 03:50 3 3a 06/11/2008 03:50 3 3b 07/11/2008 09:19 3 3c 06/11/2008 08:10 I have to delete the data creating a job from the table retaining the top two records only per each customer considering the max date.(Latest two records per customer should be retained in the table with out any dependancy on the the order) All the deleted records should be inserted into archive table which is of same structure. The expected o/p should be customer order Date 1 1a 02/10/2008 19:48 1 1d 03/10/2008 19:48 2 2c 03/11/2008 03:45 2 2d 03/11/2008 03:50 3 3b 07/11/2008 09:19 3 3c 06/11/2008 08:10 Can any one help me in this, writing the best query out of this. Thanks in advance

Answer Wiki

Thanks. We'll let you know when a new response is added.

Since you need to insert into one table and delete from another table, a single SQL command won’t be enough.

You will need a procedure.

You didn’t specify what DBMS you are working with, but if it is Sql Server, here is an example procedure:

<pre>CREATE PROCEDURE YourProcedure2
AS
DECLARE orders_Cursor CURSOR FOR
SELECT * FROM orders
ORDER BY customer,order_date DESC
DECLARE @l_orderCount integer,
@l_customer integer,
@l_previous_customer integer,
@l_order_id varchar(20),
@l_order_date date
BEGIN
SET @l_previous_customer = 0
OPEN orders_Cursor
FETCH NEXT FROM orders_Cursor INTO @l_customer,@l_order_id,@l_order_date
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF @l_customer <> @l_previous_customer
BEGIN
SET @l_previous_customer = @l_customer
SET @l_orderCount = 1
END
ELSE
BEGIN
SET @l_orderCount = @l_orderCount + 1
IF @l_orderCount > 2
BEGIN
INSERT INTO archive_orders
VALUES (@l_customer,@l_order_id,@l_order_date)
DELETE FROM orders
WHERE customer = @l_customer
AND order_id = @l_order_id
END
END
FETCH NEXT FROM orders_Cursor INTO @l_customer,@l_order_id,@l_order_date
END
CLOSE orders_Cursor
DEALLOCATE orders_Cursor
END
GO</pre>

If you are on 2005+, you could use the ROW_NUMBER feature and you won’t need a cursor.

For example, you could insert into the archive table from the following query, and then delete the same rows from the orders table:

<pre> SELECT customer,order_id
FROM
(SELECT customer,order_id,order_date,
ROW_NUMBER() OVER (partition by customer ORDER BY order_date desc) rowNumber
FROM orders) ordered_temp
WHERE rowNumber > 2</pre>

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • GIG
    Thanks for you help. That helped me at the right time. I used the row number function. Thanks once again.
    240 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following