Below is my data.
| Date | Item | Location | Seqn |
|---|---|---|---|
| 2/25/2022 17:00 | A1234 | L1 | 2 |
| 3/4/2022 18:00 | A1234 | L1 | 1 |
| 4/7/2022 3:00 | A1234 | L1 | 2 |
| 4/7/2022 3:00 | A1234 | L1 | 4 |
| 4/7/2022 3:00 | A1234 | L1 | 6 |
| 4/8/2022 11:00 | A1234 | L1 | 1 |
| 4/13/2022 8:00 | A1234 | L1 | 2 |
| 4/16/2022 17:00 | A1234 | L1 | 2 |
| 4/29/2022 15:00 | A1234 | L1 | 1 |
| 5/10/2022 5:00 | A1234 | L1 | 2 |
| 5/10/2022 5:00 | A1234 | L1 | 4 |
| 5/10/2022 5:00 | A1234 | L1 | 6 |
| 5/10/2022 5:00 | A1234 | L1 | 8 |
| 5/10/2022 5:00 | A1234 | L1 | 10 |
| 5/19/2022 4:00 | A1234 | L1 | 1 |
I need to separate using partition by and row_number based on date and sequence. As i would like to get the first record of each date. Below table would be the result of the above example:
| Date | Item | Location | Seqn | Row_number |
|---|---|---|---|---|
| 2/25/2022 17:00 | A1234 | L1 | 2 | 1 |
| 3/4/2022 18:00 | A1234 | L1 | 1 | 1 |
| 4/7/2022 3:00 | A1234 | L1 | 2 | 1 |
| 4/7/2022 3:00 | A1234 | L1 | 4 | 2 |
| 4/7/2022 3:00 | A1234 | L1 | 6 | 3 |
| 4/8/2022 11:00 | A1234 | L1 | 1 | 1 |
Here is my query:
select * from (
WITH test AS (
SELECT
row_number() OVER(
PARTITION BY Item, Location, Date, Seqn
ORDER by date, Seqn
) row_num,
date,
item,
location,
seqn
FROM
table1
)
SELECT * FROM test WHERE row_num = 1
)T1
where
item='A1234'
and location='L1'
Order by
trdt,
seqn
I have been figure out this for 2 days. Please help
CodePudding user response：
I think that what you're missing is that the date column includes a time component, and you need to remove that before using it in the PARTITION BY.
WITH
sorted AS
(
SELECT
row_number() OVER (
PARTITION BY item, location, seqn, TRUNC(date)
ORDER BY date
)
AS row_num,
date,
item,
location,
seqn
FROM
table1
)
SELECT
*
FROM
sorted
WHERE
row_num = 1
AND item = 'A1234'
AND location = 'L1'
ORDER BY
location,
item,
seqn,
date
CodePudding user response：
WITH
sorted AS
(
SELECT
row_number() OVER (
PARTITION BY item, location, TRUNC(date)
ORDER BY date,seqn
)
AS row_num,
item,
location,
seqn
FROM
table1
)
SELECT
*
FROM
sorted
WHERE row_num = 1