Skip to Main Content
  • Questions

  • ORACLE Query to return First Row for a DataSet

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Pradeep.

Asked: August 09, 2016 - 2:08 pm UTC

Last updated: August 11, 2016 - 7:05 am UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

I'm writing a query which would return first row of a set of grouped data. I tried using OVER PARTITION BY clause, but somehow I'm not getting the desired result :

select row_number() OVER(PARTITION BY leafv , value_group , l1d ,l2d ,l3d ,l4d ,l5d ,l6d ,l7d
order by leafv , value_group , l1d ,l2d ,l3d ,l4d ,l5d ,l6d ,l7d
) MYROWNUM ,
S.*
from
(SELECT
NVL(CASE
WHEN (SELECT parameter_value
FROM V_CONFIG_PARAMETERS
WHERE rownum = 1
AND parameter_name = 'MOMENT_ITEM_NUMBER_TYPE') = 'S'
THEN sku
WHEN (SELECT parameter_value
FROM V_CONFIG_PARAMETERS
WHERE rownum = 1
AND parameter_name = 'MOMENT_ITEM_NUMBER_TYPE') = 'U'
THEN upc
WHEN (SELECT parameter_value
FROM V_CONFIG_PARAMETERS
WHERE rownum = 1
AND parameter_name = 'MOMENT_ITEM_NUMBER_TYPE') = 'C'
THEN CUSTOM_PRODUCT_CODE
END,'NULLVALUE') leafv,
nvl(chain_name,'NULLVALUE') value_group,
nvl(level_10_description ,'NULLVALUE') l10d,
nvl(level_1_description,'NULLVALUE') l1d,
nvl(level_2_description,'NULLVALUE') l2d,
nvl(level_3_description,'NULLVALUE') l3d,
nvl(level_4_description,'NULLVALUE') l4d,
nvl(level_5_description,'NULLVALUE') l5d,
nvl(level_6_description,'NULLVALUE') l6d,
nvl(level_7_description,'NULLVALUE') l7d,
nvl(level_8_description,'NULLVALUE') l8d,
nvl(level_9_description,'NULLVALUE') l9d
FROM t_product_catalog_flat
order by leafv , value_group , l1d ,l2d ,l3d ,l4d ,l5d ,l6d ,l7d ,l8d ,l9d ,l10d ) S
order by MYROWNUM


Expected Result : 1 row for each set of data having same value for the group of columns ( leafv , value_group , l1d ,l2d ,l3d ,l4d ,l5d ,l6d ,l7d ,l8d ,l9d ,l10d)

But I'm getting some weird result, which doesn't match with what I was expecting. Please suggest the possible solution

and Chris said...

There's a few issues with this query:

There are repeated calls to:

SELECT parameter_value
FROM V_CONFIG_PARAMETERS
WHERE rownum = 1
AND parameter_name = 'MOMENT_ITEM_NUMBER_TYPE'


Using rownum without an order by is non-deterministic! So if there could be multiple rows for parameter MOMENT_ITEM_NUMBER_TYPE, you could get different results for each row.

In row_number(), the partition by columns are the same as the order by columns:

PARTITION BY leafv , value_group , l1d ,l2d ,l3d ,l4d ,l5d ,l6d ,l7d 
order by leafv , value_group , l1d ,l2d ,l3d ,l4d ,l5d ,l6d ,l7d


If there can be more than one row for each combination of these columns' values the output is non-deterministic. i.e. different executions can (will) assign 1 to different rows.

To avoid this you need columns in the order by not in the partition by. These must uniquely identify each row in each group defined by the partition clause.

Finally if you only want one row per group, you need to add a where clause stating this!

You need an outer query around your existing one to do this:

select * from ( ... your query here... ) where myrownum = 1;


But I'm getting some weird result, which doesn't match with what I was expecting. Please suggest the possible solution

Hopefully that's enough to get you started. But for us to tell you exactly how to fix this, we'll more information about your data. What is in the table? What are you getting? What are you expecting?

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Pradeep Kumar, August 09, 2016 - 2:55 pm UTC

Thanks for the response.

CASE statement always returns one row.

Just to simplify the query I'm rephrasing it below :

SELECT row_number() OVER( order by leafv , value_group , l1d ,l2d ,l3d ,l4d ,l5d ,l6d ,l7d ) MYROWNUM ,
S.*
FROM
(SELECT NVL(SKU, 'NULLVALUE') leafv,
NVL(chain_name,'NULLVALUE') value_group,
NVL(level_10_description ,'NULLVALUE') l10d,
NVL(level_1_description,'NULLVALUE') l1d,
NVL(level_2_description,'NULLVALUE') l2d,
NVL(level_3_description,'NULLVALUE') l3d,
NVL(level_4_description,'NULLVALUE') l4d,
NVL(level_5_description,'NULLVALUE') l5d,
NVL(level_6_description,'NULLVALUE') l6d,
NVL(level_7_description,'NULLVALUE') l7d,
NVL(level_8_description,'NULLVALUE') l8d,
NVL(level_9_description,'NULLVALUE') l9d
FROM t_product_catalog_flat
ORDER BY leafv ,
value_group ,
l1d ,
l2d ,
l3d ,
l4d ,
l5d ,
l6d ,
l7d ,
l8d ,
l9d ,
l10d
) S
WHERE MYROWNUM=1

There are duplicates for the combination of columns (leafv , value_group , l1d ,l2d ,l3d ,l4d ,l5d ,l6d ,l7d )

I want just one row for each of these grouped value.

Above query is throwing error :ORA-00904: "MYROWNUM": invalid identifier.

When I remove WHERE MYROWNUM=1 , query returns the result but computes MYROWNUM with distinct values even if there are duplicates for above set of columns



Chris Saxon
August 09, 2016 - 3:38 pm UTC

You need another outer query around this one that restricts to just first rows!

Pradeep Kumar, August 09, 2016 - 5:45 pm UTC

No, It didn't get the desired result.

This is what the tables and test data we have. Out of 19 rows, 3 are duplicates ( combination of values in all the columns) .

I'm expecting 16 rows as the result of query, please let me know

CREATE TABLE test_partition
(
SKU VARCHAR2(50),
chain_name VARCHAR2(50),
level_1_description VARCHAR2(50),
level_2_description VARCHAR2(50),
level_3_description VARCHAR2(50),
level_4_description VARCHAR2(50),
level_5_description VARCHAR2(50),
level_6_description VARCHAR2(50),
level_7_description VARCHAR2(50),
level_8_description VARCHAR2(50),
level_9_description VARCHAR2(50),
level_10_description VARCHAR2(50)
);
insert into test_partition values ('001456','AUTO_CHAIN','TEXAS','IRVING','Test Store Alpha',NULL,NULL,NULL,NULL,NULL,NULL,NULL);

insert into test_partition values ('123','TEST','test','wef','wefg',NULL,NULL,NULL,NULL,NULL,NULL,NULL);

insert into test_partition values ('124','TEST','test','wef','wef',NULL,NULL,NULL,NULL,NULL,NULL,NULL);

insert into test_partition values ('23234','AUTO_CHAIN','TEXAS','IRVING','Test Store Alpha',NULL,NULL,NULL,NULL,NULL,NULL,NULL);

insert into test_partition values ('5R65','AUTO_CHAIN','GEF','NEW',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);

insert into test_partition values ('GH','AUTO_CHAIN','TEXAS','IRVING','Test Store Alpha',NULL,NULL,NULL,NULL,NULL,NULL,NULL);

insert into test_partition values ('MN','AUTO_CHAIN','TEXAS','IRVING','Test Store Alpha',NULL,NULL,NULL,NULL,NULL,NULL,NULL);

insert into test_partition values ('MNS','test','wef','wefg',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);


insert into test_partition values ('SAS','AUTO_CHAIN','TEXAS','IRVING','Test Store Alpha',NULL,NULL,NULL,NULL,NULL,NULL,NULL);

insert into test_partition values ('SH','AUTO_CHAIN','TEXAS','IRVING','Test Store Alpha',NULL,NULL,NULL,NULL,NULL,NULL,NULL);

insert into test_partition values ('TEST','AUTO_CHAIN','TEXAS','IRVING','Test Store Alpha',NULL,NULL,NULL,NULL,NULL,NULL,NULL);


insert into test_partition values ('mnz','AUTO_CHAIN','TEXAS','IRVING','Test Store Alpha',NULL,NULL,NULL,NULL,NULL,NULL,NULL);

insert into test_partition values (NULL,'ERE','GEF','NEW','Test','test','test','test','test','test','test','LEVEL10');

insert into test_partition values (NULL,'AUTO_CHAIN','level1 testing','level2 testing','level3 testing','level4 testing','level5 testing','level6 testing','level7 testing','level8 testing','level9 testing','level10 testing');

insert into test_partition values (NULL,'AUTO_CHAIN','level1 testing','level2 testing','level3 testing','level4 testing','level5 testing','level6 testing','level7 testing','level8 testing','level9 testing','level10 testing');


insert into test_partition values (NULL,'POINTGIFTHAIN','gold','silver','bronze','testsampletest',NULL,NULL,NULL,NULL,NULL,NULL);


insert into test_partition values (NULL,'POINTGIFTHAIN','gold','silver','bronze','testsampletest',NULL,NULL,NULL,NULL,NULL,NULL);


insert into test_partition values (NULL,'POINTGIFTHAIN','gold class','silver class','bronze','point',NULL,NULL,NULL,NULL,NULL,NULL);

insert into test_partition values (NULL,'POINTGIFTHAIN','gold class','silver class','bronze','point',NULL,NULL,NULL,NULL,NULL,NULL);

Chris Saxon
August 10, 2016 - 9:42 am UTC

I believe the query in the reply below has the answer you're looking for.

For Pradeep

Muhammad Riaz, August 10, 2016 - 8:23 am UTC

Are you looking for something like below?

SELECT * FROM
(SELECT row_number() OVER(partition by leafv , value_group , l1d ,l2d ,l3d ,l4d ,l5d ,l6d ,l7d order by leafv , value_group , l1d ,l2d ,l3d ,l4d ,l5d ,l6d ,l7d ) MYROWNUM ,
S.*
FROM
(SELECT NVL(SKU, 'NULLVALUE') leafv,
NVL(chain_name,'NULLVALUE') value_group,
NVL(level_10_description ,'NULLVALUE') l10d,
NVL(level_1_description,'NULLVALUE') l1d,
NVL(level_2_description,'NULLVALUE') l2d,
NVL(level_3_description,'NULLVALUE') l3d,
NVL(level_4_description,'NULLVALUE') l4d,
NVL(level_5_description,'NULLVALUE') l5d,
NVL(level_6_description,'NULLVALUE') l6d,
NVL(level_7_description,'NULLVALUE') l7d,
NVL(level_8_description,'NULLVALUE') l8d,
NVL(level_9_description,'NULLVALUE') l9d
FROM test_partition
ORDER BY leafv ,
value_group ,
l1d ,
l2d ,
l3d ,
l4d ,
l5d ,
l6d ,
l7d ,
l8d ,
l9d ,
l10d
) S
)
WHERE MYROWNUM=1
Chris Saxon
August 10, 2016 - 9:43 am UTC

It gives me 16 rows on the dataset above, so I'm guessing yes!

Pradeep Kumar, August 10, 2016 - 4:02 pm UTC

Yes, suggested query gives expected result.

Thanks so much.
Chris Saxon
August 11, 2016 - 7:05 am UTC

Glad we could help
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.