Help with some ordering

#1

I have this query:

SELECT customer_id, co.order_id, p.product_id, ol.status, p.code,
p.subproduct_id,
FIRST_VALUE(ol.status)
OVER (PARTITION BY customer_id, subproduct_id ORDER BY order_date
DESC) LAST_STATUS
FROM engine.customer_order co, engine.order_line ol, engine.product p
WHERE co.order_id = ol.order_id
AND ol.product_id = p.product_id
AND p.subproduct_id IN (209, 211, 216, 217, 220, 222, 223, 224, 226)
ORDER BY customer_id;

I want to get the status of the most recent record for that customer.
There can be more than 1 record for that customer with the same
subproduct ID.

A customer may have ordered a product, canceled it and re-ordered it
at a later date.....I would want the status of the most recent, along
with the other information above.

This query is giving me all the records. I thought this query would
partition by customer ID / subproduct ID, order by the order date, and
give me the first record (FIRST_VALUE).

Any idea why I am not getting this??

#2

a... (AT) mickeyschicago (DOT) com wrote:

Quote:

I have this query:

SELECT customer_id, co.order_id, p.product_id, ol.status,
p.code, p.subproduct_id,
* FIRST_VALUE(ol.status)
* OVER (PARTITION BY customer_id, subproduct_id
ORDER BY order_date
DESC) LAST_STATUS
FROM engine.customer_order co, engine.order_line ol,
engine.product p
WHERE co.order_id = ol.order_id
* AND ol.product_id = p.product_id
* AND p.subproduct_id IN (209, 211, 216, 217, 220, 222,
223, 224, 226)
ORDER BY customer_id;

I want to get the status of the most recent record for
that customer.
There can be more than 1 record for that customer with
the same subproduct ID.

A customer may have ordered a product, canceled it and
re-ordered it at a later date.....I would want the
status of the most recent, along with the other
information above.

This query is giving me all the records. *I thought
this query would partition by customer ID / subproduct
ID, order by the order date, and give me the first
record (FIRST_VALUE).

The analytical function _is_ partitioning and giving you
the first status within that partition by the the
order date.

Quote:

Any idea why I am not getting this??

Analytic functions don't reduce rows. Indeed they
give you group information for _every_ row.

Select the status as normal, but use row_number()
instead of first_value. Then do an outer select
on your query looking for row numbers of 1.

Note that this might still give you multiple rows
if a customer orders the same product multiple
times on the same order_date. In which case it's
generally best to refine the order by in the
row_number(), e.g. by order_id desc.

--
Peter

#3

a... (AT) mickeyschicago (DOT) com wrote:

Quote:

I have this query:

SELECT customer_id, co.order_id, p.product_id, ol.status,
p.code, p.subproduct_id,
* FIRST_VALUE(ol.status)
* OVER (PARTITION BY customer_id, subproduct_id
ORDER BY order_date
DESC) LAST_STATUS
FROM engine.customer_order co, engine.order_line ol,
engine.product p
WHERE co.order_id = ol.order_id
* AND ol.product_id = p.product_id
* AND p.subproduct_id IN (209, 211, 216, 217, 220, 222,
223, 224, 226)
ORDER BY customer_id;

I want to get the status of the most recent record for
that customer.
There can be more than 1 record for that customer with
the same subproduct ID.

A customer may have ordered a product, canceled it and
re-ordered it at a later date.....I would want the
status of the most recent, along with the other
information above.

This query is giving me all the records. *I thought
this query would partition by customer ID / subproduct
ID, order by the order date, and give me the first
record (FIRST_VALUE).

The analytical function _is_ partitioning and giving you
the first status within that partition by the the
order date.

Quote:

Any idea why I am not getting this??

Analytic functions don't reduce rows. Indeed they
give you group information for _every_ row.

Select the status as normal, but use row_number()
instead of first_value. Then do an outer select
on your query looking for row numbers of 1.

Note that this might still give you multiple rows
if a customer orders the same product multiple
times on the same order_date. In which case it's
generally best to refine the order by in the
row_number(), e.g. by order_id desc.

--
Peter

#4

a... (AT) mickeyschicago (DOT) com wrote:

Quote:

I have this query:

SELECT customer_id, co.order_id, p.product_id, ol.status,
p.code, p.subproduct_id,
* FIRST_VALUE(ol.status)
* OVER (PARTITION BY customer_id, subproduct_id
ORDER BY order_date
DESC) LAST_STATUS
FROM engine.customer_order co, engine.order_line ol,
engine.product p
WHERE co.order_id = ol.order_id
* AND ol.product_id = p.product_id
* AND p.subproduct_id IN (209, 211, 216, 217, 220, 222,
223, 224, 226)
ORDER BY customer_id;

I want to get the status of the most recent record for
that customer.
There can be more than 1 record for that customer with
the same subproduct ID.

A customer may have ordered a product, canceled it and
re-ordered it at a later date.....I would want the
status of the most recent, along with the other
information above.

This query is giving me all the records. *I thought
this query would partition by customer ID / subproduct
ID, order by the order date, and give me the first
record (FIRST_VALUE).

The analytical function _is_ partitioning and giving you
the first status within that partition by the the
order date.

Quote:

Any idea why I am not getting this??

Analytic functions don't reduce rows. Indeed they
give you group information for _every_ row.

Select the status as normal, but use row_number()
instead of first_value. Then do an outer select
on your query looking for row numbers of 1.

Note that this might still give you multiple rows
if a customer orders the same product multiple
times on the same order_date. In which case it's
generally best to refine the order by in the
row_number(), e.g. by order_id desc.

--
Peter

#5

a... (AT) mickeyschicago (DOT) com wrote:

Quote:

I have this query:

SELECT customer_id, co.order_id, p.product_id, ol.status,
p.code, p.subproduct_id,
* FIRST_VALUE(ol.status)
* OVER (PARTITION BY customer_id, subproduct_id
ORDER BY order_date
DESC) LAST_STATUS
FROM engine.customer_order co, engine.order_line ol,
engine.product p
WHERE co.order_id = ol.order_id
* AND ol.product_id = p.product_id
* AND p.subproduct_id IN (209, 211, 216, 217, 220, 222,
223, 224, 226)
ORDER BY customer_id;

I want to get the status of the most recent record for
that customer.
There can be more than 1 record for that customer with
the same subproduct ID.

A customer may have ordered a product, canceled it and
re-ordered it at a later date.....I would want the
status of the most recent, along with the other
information above.

This query is giving me all the records. *I thought
this query would partition by customer ID / subproduct
ID, order by the order date, and give me the first
record (FIRST_VALUE).

The analytical function _is_ partitioning and giving you
the first status within that partition by the the
order date.

Quote:

Any idea why I am not getting this??

Analytic functions don't reduce rows. Indeed they
give you group information for _every_ row.

Select the status as normal, but use row_number()
instead of first_value. Then do an outer select
on your query looking for row numbers of 1.

Note that this might still give you multiple rows
if a customer orders the same product multiple
times on the same order_date. In which case it's
generally best to refine the order by in the
row_number(), e.g. by order_id desc.

--
Peter

#6

<art (AT) mickeyschicago (DOT) com> a écrit dans le message de news: 0f59e550-e2e5-4c9f-b873-3fc2d7b5846f...oglegroups.com...

Quote:

I have this query:

SELECT customer_id, co.order_id, p.product_id, ol.status, p.code,
p.subproduct_id,
FIRST_VALUE(ol.status)
OVER (PARTITION BY customer_id, subproduct_id ORDER BY order_date
DESC) LAST_STATUS
FROM engine.customer_order co, engine.order_line ol, engine.product p
WHERE co.order_id = ol.order_id
AND ol.product_id = p.product_id
AND p.subproduct_id IN (209, 211, 216, 217, 220, 222, 223, 224, 226)
ORDER BY customer_id;

I want to get the status of the most recent record for that customer.
There can be more than 1 record for that customer with the same
subproduct ID.

A customer may have ordered a product, canceled it and re-ordered it
at a later date.....I would want the status of the most recent, along
with the other information above.

This query is giving me all the records. I thought this query would
partition by customer ID / subproduct ID, order by the order date, and
give me the first record (FIRST_VALUE).

Any idea why I am not getting this??

Have a look at ROW_NUMBER/RANK/DENSE_RANK functions.

Regards
Michel

#7

<art (AT) mickeyschicago (DOT) com> a écrit dans le message de news: 0f59e550-e2e5-4c9f-b873-3fc2d7b5846f...oglegroups.com...

Quote:

I have this query:

SELECT customer_id, co.order_id, p.product_id, ol.status, p.code,
p.subproduct_id,
FIRST_VALUE(ol.status)
OVER (PARTITION BY customer_id, subproduct_id ORDER BY order_date
DESC) LAST_STATUS
FROM engine.customer_order co, engine.order_line ol, engine.product p
WHERE co.order_id = ol.order_id
AND ol.product_id = p.product_id
AND p.subproduct_id IN (209, 211, 216, 217, 220, 222, 223, 224, 226)
ORDER BY customer_id;

I want to get the status of the most recent record for that customer.
There can be more than 1 record for that customer with the same
subproduct ID.

A customer may have ordered a product, canceled it and re-ordered it
at a later date.....I would want the status of the most recent, along
with the other information above.

This query is giving me all the records. I thought this query would
partition by customer ID / subproduct ID, order by the order date, and
give me the first record (FIRST_VALUE).

Any idea why I am not getting this??

Have a look at ROW_NUMBER/RANK/DENSE_RANK functions.

Regards
Michel

#8

<art (AT) mickeyschicago (DOT) com> a écrit dans le message de news: 0f59e550-e2e5-4c9f-b873-3fc2d7b5846f...oglegroups.com...

Quote:

I have this query:

SELECT customer_id, co.order_id, p.product_id, ol.status, p.code,
p.subproduct_id,
FIRST_VALUE(ol.status)
OVER (PARTITION BY customer_id, subproduct_id ORDER BY order_date
DESC) LAST_STATUS
FROM engine.customer_order co, engine.order_line ol, engine.product p
WHERE co.order_id = ol.order_id
AND ol.product_id = p.product_id
AND p.subproduct_id IN (209, 211, 216, 217, 220, 222, 223, 224, 226)
ORDER BY customer_id;

I want to get the status of the most recent record for that customer.
There can be more than 1 record for that customer with the same
subproduct ID.

A customer may have ordered a product, canceled it and re-ordered it
at a later date.....I would want the status of the most recent, along
with the other information above.

This query is giving me all the records. I thought this query would
partition by customer ID / subproduct ID, order by the order date, and
give me the first record (FIRST_VALUE).

Any idea why I am not getting this??

Have a look at ROW_NUMBER/RANK/DENSE_RANK functions.

Regards
Michel

#9

<art (AT) mickeyschicago (DOT) com> a écrit dans le message de news: 0f59e550-e2e5-4c9f-b873-3fc2d7b5846f...oglegroups.com...

Quote:

I have this query:

SELECT customer_id, co.order_id, p.product_id, ol.status, p.code,
p.subproduct_id,
FIRST_VALUE(ol.status)
OVER (PARTITION BY customer_id, subproduct_id ORDER BY order_date
DESC) LAST_STATUS
FROM engine.customer_order co, engine.order_line ol, engine.product p
WHERE co.order_id = ol.order_id
AND ol.product_id = p.product_id
AND p.subproduct_id IN (209, 211, 216, 217, 220, 222, 223, 224, 226)
ORDER BY customer_id;

I want to get the status of the most recent record for that customer.
There can be more than 1 record for that customer with the same
subproduct ID.

A customer may have ordered a product, canceled it and re-ordered it
at a later date.....I would want the status of the most recent, along
with the other information above.

This query is giving me all the records. I thought this query would
partition by customer ID / subproduct ID, order by the order date, and
give me the first record (FIRST_VALUE).

Any idea why I am not getting this??

Have a look at ROW_NUMBER/RANK/DENSE_RANK functions.

Regards
Michel

#10

<art (AT) mickeyschicago (DOT) com> schreef in bericht
news:0f59e550-e2e5-4c9f-b873-3fc2d7b5846f (AT) y71g2000hsa (DOT) googlegroups.com...

Quote:

I have this query:

SELECT customer_id, co.order_id, p.product_id, ol.status, p.code,
p.subproduct_id,
FIRST_VALUE(ol.status)
OVER (PARTITION BY customer_id, subproduct_id ORDER BY order_date
DESC) LAST_STATUS
FROM engine.customer_order co, engine.order_line ol, engine.product p
WHERE co.order_id = ol.order_id
AND ol.product_id = p.product_id
AND p.subproduct_id IN (209, 211, 216, 217, 220, 222, 223, 224, 226)
ORDER BY customer_id;

I want to get the status of the most recent record for that customer.
There can be more than 1 record for that customer with the same
subproduct ID.

A customer may have ordered a product, canceled it and re-ordered it
at a later date.....I would want the status of the most recent, along
with the other information above.

This query is giving me all the records. I thought this query would
partition by customer ID / subproduct ID, order by the order date, and
give me the first record (FIRST_VALUE).

Any idea why I am not getting this??

No, and I don't have any idea why you keep posting questions without
following up to most of the responses you get. And why you keep changing
your address with every n-th post, unless this is related to the
none-responsiveness.

Shakespeare