The row_number in the subselect of an Oracle SQL Query is not picking the exact record.

I've got the query below against the table attached, I'm still getting mixed results as to the customer that has the product although I'm picking on the first instance of the transaction, the problem is only on days when the Model has exchanged hands on the same day.    

select
    modelnumber,
      releasenumber,
      deliverydate,
      deliverytime,
      customer
    from
    (select
    modelnumber,
      releasenumber,
      deliverydate,
      deliverytime,
      customer,
    row_number() over(partition by modelnumber order by deliverydate||deliverytime desc) rn from
    deliveries)
    where rn=1 And
      etc......
Roberto Madro R.Programmer AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
I believe the issue is the string concatenation.  That returns a string not a date.

You need to order by a date data type.

Try:
...
 row_number() over(partition by modelnumber order by to_date(deliverydate||deliverytime,'MM/DD/YYYYHH:MI:SS AM') desc) rn from
...

assuming the actual strings are in the same format shown in the Excel Spreadsheet.
0
Roberto Madro R.Programmer AnalystAuthor Commented:
slightwv, out of the box I was getting ORA-01849: hour must be between 1 and 12, I then went with "to_timestamp" but kept getting the same error.
0
slightwv (䄆 Netminder) Commented:
The format mask has to match the data.

Post an example of the actual data in the columns.  Not in Excel.

select  deliverydate, deleverytime from deliveries where rownum<6;
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Roberto Madro R.Programmer AnalystAuthor Commented:
query output.
0
slightwv (䄆 Netminder) Commented:
Make the format mask look like the data.

Given that data try:
row_number() over(partition by modelnumber order by to_date(deliverydate||deliverytime,'YYYYMMDDHH24MISS') desc) rn from
0
slightwv (䄆 Netminder) Commented:
If that still isn't right, you will need to come up with the mask that works for your data.

Just about anything you can imagine is allowed.  Here is the documentation that shows allowed values:
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/Format-Models.html#GUID-49B32A81-0904-433E-B7FE-51606672183A
0
Roberto Madro R.Programmer AnalystAuthor Commented:
Now its throwing me ORA-01850 hour must be between 0 and 23.
0
SujithData ArchitectCommented:
what is are the data types of deliverydate and deliverytime?

your order by should depend on the data type and the concatenated string should be casted to a date type for the ordering to work.

if Delievery date is a DATE column; you will have to do a TO_CHAR to convert it to a string at first.

Assuming
deliverydate is DATE type - apply TO_CHAR(deliverydate , 'YYYYMMDD')
deliverytime is VARCHAR type - its format is HH:MI AM

your order by can be - order by TO_DATE(TO_CHAR(deliverydate , 'YYYYMMDD')||deliverytime, 'YYYYMMDDHH:MI AM') desc
0
slightwv (䄆 Netminder) Commented:
>>Now its throwing me ORA-01850 hour must be between 0 and 23.

One more guess:
row_number() over(partition by modelnumber order by to_date(deliverydate||lpad(deliverytime,6,'0'),'YYYYMMDDHH24MISS')  desc) rn from
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SujithData ArchitectCommented:
>> Now its throwing me ORA-01850 hour must be between 0 and 23.

because your data is not in 24Hour format it is 12 hr format. YOu should use AM/PM mask HH:MI AM instead of HH24:MI
0
slightwv (䄆 Netminder) Commented:
@Sujith, look at the actual sample data in #a42542756

It looks like 24 hour clock.  I believe the issue is with the second sample row that is missing the leading 0.  The LPAD I posted should address that.
0
Roberto Madro R.Programmer AnalystAuthor Commented:
Slightwv, the posting ID: 42542773 solution worked, but I'm getting less data, something like 50 records less than what I know should be there, I'm doing some additional testing and will post my findings.

Thx
0
slightwv (䄆 Netminder) Commented:
Might be a data issue?
0
awking00Information Technology SpecialistCommented:
From the data you posted in ID: 42542756, what would you expect as output?
0
Roberto Madro R.Programmer AnalystAuthor Commented:
I'm expecting to only pickup the very latest transaction based the date and time, the problem is when a modelnumber is switched hands in the same day, the current script is picking up the earlier transaction not the latest transaction.
0
slightwv (䄆 Netminder) Commented:
We need sample data and the expected results.  That way we can set up a test case and provide tested SQL that generates the exact results you provide.
0
Roberto Madro R.Programmer AnalystAuthor Commented:
The sample data is in an Excel spreadsheet, and here's the description of the table columns involved.
1-Modelnumber Varchar(15)
2-Releasenumber Varchar (6)
3-Deliverydate number(8)
4-Deliverytime number(6)
5-Customer Varchar(15)
For_EE.xlsx
0
slightwv (䄆 Netminder) Commented:
As we have already pointed out:  Excel doesn't work.  It tries to guess at what the data is and converts it for you.

We need raw data as it appears in the actual table.  Make sure the same data you provide shows the problem you are having.
0
awking00Information Technology SpecialistCommented:
I don't want to know what you're expecting in words, but actual results from the query you want. I was really hoping to see something like this -

ModelNumber Deliverydate Deliverytime
3661628         20040408       203000
3661751         20040410       094800
3661521         20040407       171800
2878598         20040407       145700

Open in new window


With the explanation that ModelNumber 3661521 is returned with the delivery time of 171800 because the same ModelNumber and DeliveryDate was also returned but at a delivery time of 145300 which was earlier.

If that's the case, then I think you just need to add the deliverydate to the row_number() partition in slightwv's solution -
select modelnumber, deliverydate, delivery time from
(select modelnumber, deliverydate, delivery time,
 row_number() over(partition by modelnumber, deliverydate order by to_date(deliverydate||lpad(deliverytime,6,'0'),'YYYYMMDDHH24MISS') desc) rn from deliveries)
where rn = 1;
0
slightwv (䄆 Netminder) Commented:
OK, I took a look at the attached spreadsheet and mocked up a test case.

Here it is:
/*
drop table tab1 purge;
create table tab1(
Modelnumber Varchar2(25),
Releasenumber Varchar2(10),
Deliverydate number(8),
Deliverytime number(6),
Customer Varchar2(15)
);

insert into tab1 values('AB-CA12W115918010925','E3087TKS11',20180401,13029,'Alpha');
insert into tab1 values('AB-CA13W115918080790','E0336TKS12',20180401,13032,'RAILEX');
insert into tab1 values('AB-CA14W115918021400','E2555TKS13',20180401,64953,'TRITON');
insert into tab1 values('AB-CA15W115918081536','E2555TKS14',20180401,64956,'MAXED');
insert into tab1 values('AB-CA16W115918021398','E2555TKS15',20180401,65000,'WILBURN');

insert into tab1 values('AB-CA17W115918081538','E2555TKS16',20180410,65006,'MADDOx');
insert into tab1 values('AB-CA17W115918081538','E2555TKS16',20180410,65011,'Alpha');

insert into tab1 values('AB-CA19W115918081537','E2555TKS18',20180410,65015,'RAILEX');
insert into tab1 values('AB-CA20W115918081535','E2555TKS19',20180410,65019,'TRITON');
insert into tab1 values('AB-CA20W115918081535','E2555TKS19',20180410,65021,'MAXED');
insert into tab1 values('AB-CA22W115918050925','E0226TKS21',20180410,65617,'WILBURN');
insert into tab1 values('AB-CA23W115918050930','E0226TKS22',20180410,65618,'MADDOx');
insert into tab1 values('AB-CA24W115918050931','E0226TKS23',20180410,65621,'Alpha');
insert into tab1 values('AB-CA24W115918050931','E0226TKS23',20180410,65624,'RAILEX');
insert into tab1 values('AB-CA24W115918050931','E0226TKS23',20180410,65627,'TRITON');
insert into tab1 values('AB-CA27W115918050934','E0226TKS26',20180410,65629,'MAXED');
insert into tab1 values('AB-CA28W115918050937','E0226TKS27',20180410,65632,'WILBURN');
insert into tab1 values('AB-CA28W115918050937','E0226TKS27',20180411,65635,'MADDOx');
insert into tab1 values('AB-CA30W115918021394','E0226TKS29',20180412,65638,'Alpha');
insert into tab1 values('AB-CA31W115918021395','E0226TKS30',20180413,65641,'RAILEX');
insert into tab1 values('AB-CA32W115918021397','E0226TKS31',20180414,65643,'TRITON');
insert into tab1 values('AB-CA33W115918021404','E0226TKS32',20180415,65646,'MAXED');
insert into tab1 values('AB-CA34W115918021405','E0226TKS33',20180416,65648,'WILBURN');
insert into tab1 values('AB-CA35W115918021410','E0226TKS34',20180417,65650,'MADDOx');
insert into tab1 values('AB-CA36W115918021411','E0226TKS35',20180418,65653,'Alpha');
insert into tab1 values('AB-CA37W115918081540','E0226TKS36',20180419,65656,'RAILEX');
insert into tab1 values('AB-CA38W115918021251','E0336TKS37',20180420,163441,'TRITON');
insert into tab1 values('AB-CA39W115918081303','E0336TKS38',20180421,163454,'MAXED');
insert into tab1 values('AB-CA40W115918200424','E0336TKS39',20180422,163512,'WILBURN');
insert into tab1 values('AB-CA41W115918021128','E0226TKS40',20180423,163521,'MADDOx');
commit;
*/

Open in new window


Now I execute my SQL, here are the results:
MODELNUMBER               RELEASENUM DELIVERYDATE DELIVERYTIME CUSTOMER
------------------------- ---------- ------------ ------------ ---------------
AB-CA12W115918010925      E3087TKS11     20180401        13029 Alpha
AB-CA13W115918080790      E0336TKS12     20180401        13032 RAILEX
AB-CA14W115918021400      E2555TKS13     20180401        64953 TRITON
AB-CA15W115918081536      E2555TKS14     20180401        64956 MAXED
AB-CA16W115918021398      E2555TKS15     20180401        65000 WILBURN
AB-CA17W115918081538      E2555TKS16     20180410        65011 Alpha
AB-CA19W115918081537      E2555TKS18     20180410        65015 RAILEX
AB-CA20W115918081535      E2555TKS19     20180410        65021 MAXED
AB-CA22W115918050925      E0226TKS21     20180410        65617 WILBURN
AB-CA23W115918050930      E0226TKS22     20180410        65618 MADDOx
AB-CA24W115918050931      E0226TKS23     20180410        65627 TRITON
AB-CA27W115918050934      E0226TKS26     20180410        65629 MAXED
AB-CA28W115918050937      E0226TKS27     20180411        65635 MADDOx
AB-CA30W115918021394      E0226TKS29     20180412        65638 Alpha
AB-CA31W115918021395      E0226TKS30     20180413        65641 RAILEX
AB-CA32W115918021397      E0226TKS31     20180414        65643 TRITON
AB-CA33W115918021404      E0226TKS32     20180415        65646 MAXED
AB-CA34W115918021405      E0226TKS33     20180416        65648 WILBURN
AB-CA35W115918021410      E0226TKS34     20180417        65650 MADDOx
AB-CA36W115918021411      E0226TKS35     20180418        65653 Alpha
AB-CA37W115918081540      E0226TKS36     20180419        65656 RAILEX
AB-CA38W115918021251      E0336TKS37     20180420       163441 TRITON
AB-CA39W115918081303      E0336TKS38     20180421       163454 MAXED
AB-CA40W115918200424      E0336TKS39     20180422       163512 WILBURN
AB-CA41W115918021128      E0226TKS40     20180423       163521 MADDOx

25 rows selected.

Open in new window



Using awking00's suggestion, here are the results:
MODELNUMBER               RELEASENUM DELIVERYDATE DELIVERYTIME CUSTOMER
------------------------- ---------- ------------ ------------ ---------------
AB-CA12W115918010925      E3087TKS11     20180401        13029 Alpha
AB-CA13W115918080790      E0336TKS12     20180401        13032 RAILEX
AB-CA14W115918021400      E2555TKS13     20180401        64953 TRITON
AB-CA15W115918081536      E2555TKS14     20180401        64956 MAXED
AB-CA16W115918021398      E2555TKS15     20180401        65000 WILBURN
AB-CA17W115918081538      E2555TKS16     20180410        65011 Alpha
AB-CA19W115918081537      E2555TKS18     20180410        65015 RAILEX
AB-CA20W115918081535      E2555TKS19     20180410        65021 MAXED
AB-CA22W115918050925      E0226TKS21     20180410        65617 WILBURN
AB-CA23W115918050930      E0226TKS22     20180410        65618 MADDOx
AB-CA24W115918050931      E0226TKS23     20180410        65627 TRITON
AB-CA27W115918050934      E0226TKS26     20180410        65629 MAXED
AB-CA28W115918050937      E0226TKS27     20180410        65632 WILBURN
AB-CA28W115918050937      E0226TKS27     20180411        65635 MADDOx
AB-CA30W115918021394      E0226TKS29     20180412        65638 Alpha
AB-CA31W115918021395      E0226TKS30     20180413        65641 RAILEX
AB-CA32W115918021397      E0226TKS31     20180414        65643 TRITON
AB-CA33W115918021404      E0226TKS32     20180415        65646 MAXED
AB-CA34W115918021405      E0226TKS33     20180416        65648 WILBURN
AB-CA35W115918021410      E0226TKS34     20180417        65650 MADDOx
AB-CA36W115918021411      E0226TKS35     20180418        65653 Alpha
AB-CA37W115918081540      E0226TKS36     20180419        65656 RAILEX
AB-CA38W115918021251      E0336TKS37     20180420       163441 TRITON
AB-CA39W115918081303      E0336TKS38     20180421       163454 MAXED
AB-CA40W115918200424      E0336TKS39     20180422       163512 WILBURN
AB-CA41W115918021128      E0226TKS40     20180423       163521 MADDOx

26 rows selected.

Open in new window


His added an extra row.  Is that the correct results from the sample data provided?
0
Roberto Madro R.Programmer AnalystAuthor Commented:
The posted output of both extracts are right on the money, it's exactly what I'm hoping for, but so far not getting although I executed the script as called for in ID:42544191 by awking00, with that in mind, I'm starting to believe what Slightwv said in ID:42542934, we'll see, I'm looking at other ways of accomplishing this.
Thx
0
slightwv (䄆 Netminder) Commented:
>>I'm looking at other ways of accomplishing this.

No need to look.  You know what data is missing from your result set.

Add examples to the test case I provided then provide expected results.  We can probably handle it from there.
0
awking00Information Technology SpecialistCommented:
If you think there might be a data problem, then post the actual data (it doesn't appear to be proprietary) and indicate which records should not appear (or only the records that should appear) in the query results. Please don't post the data as a picture, either in text format or an excel spreadsheet will do.
0
slightwv (䄆 Netminder) Commented:
>>or an excel spreadsheet will do.

I prefer a TXT file.  As seen above:  Excel can "change" the data.
0
Roberto Madro R.Programmer AnalystAuthor Commented:
Enclosed is a tab-separated txt file, hope this will help, to bring the point home, look at Modelnumber AB-CA17W115918081538 with Deliverydate of 20180410 & Deliverytime of 5006 to MADDOX & 5011 to Aplha, the query is still picking up the earlier transaction (MADDOX).For_EE.txt
0
slightwv (䄆 Netminder) Commented:
If you look at the results I posted from my test runs, both show:
CA17W115918081538      E2555TKS16     20180410        65011 Alpha

Maybe something else you are adding to your query is preventing that row from coming back?
0
awking00Information Technology SpecialistCommented:
Here it is including all of the fields -

SQL> select * from deliveries;

MODELNUMBER          RELEASENUMBER        DELIVERY DELIVE CUSTOMER
-------------------- -------------------- -------- ------ ----------
AB-CA12W115918010925 E3087TKS11           20180401 13029  Alpha
AB-CA13W115918080790 E0336TKS12           20180401 13032  RAILEX
AB-CA14W115918021400 E2555TKS13           20180401 64953  TRITON
AB-CA15W115918081536 E2555TKS14           20180401 64956  MAXED
AB-CA16W115918021398 E2555TKS15           20180401 65000  WILBURN
AB-CA17W115918081538 E2555TKS16           20180410 65006  MADDOx
AB-CA17W115918081538 E2555TKS16           20180410 65011  Alpha
AB-CA19W115918081537 E2555TKS18           20180410 65015  RAILEX
AB-CA20W115918081535 E2555TKS19           20180410 65019  TRITON
AB-CA20W115918081535 E2555TKS19           20180410 65021  MAXED
AB-CA22W115918050925 E0226TKS21           20180410 65617  WILBURN
AB-CA23W115918050930 E0226TKS22           20180410 65618  MADDOx
AB-CA24W115918050931 E0226TKS23           20180410 65621  Alpha
AB-CA24W115918050931 E0226TKS23           20180410 65624  RAILEX
AB-CA24W115918050931 E0226TKS23           20180410 65627  TRITON
AB-CA27W115918050934 E0226TKS26           20180410 65629  MAXED
AB-CA28W115918050937 E0226TKS27           20180410 65632  WILBURN
AB-CA28W115918050937 E0226TKS27           20180411 65635  MADDOx
AB-CA30W115918021394 E0226TKS29           20180412 65638  Alpha
AB-CA31W115918021395 E0226TKS30           20180413 65641  RAILEX
AB-CA32W115918021397 E0226TKS31           20180414 65643  TRITON
AB-CA33W115918021404 E0226TKS32           20180415 65646  MAXED
AB-CA34W115918021405 E0226TKS33           20180416 65648  WILBURN
AB-CA35W115918021410 E0226TKS34           20180417 65650  MADDOx
AB-CA36W115918021411 E0226TKS35           20180418 65653  Alpha
AB-CA37W115918081540 E0226TKS36           20180419 65656  RAILEX
AB-CA38W115918021251 E0336TKS37           20180420 163441 TRITON
AB-CA39W115918081303 E0336TKS38           20180421 163454 MAXED
AB-CA40W115918200424 E0336TKS39           20180422 163512 WILBURN
AB-CA41W115918021128 E0226TKS40           20180423 163521 Maddox

SQL> select modelnumber, releasenumber, deliverydate, deliverytime, customer from
  2   (select modelnumber, deliverydate, releasenumber, deliverytime, customer,
  3    row_number() over(partition by modelnumber, deliverydate order by to_date(deliverydate||lpad(deliverytime,6,'0'),
'YYYYMMDDHH24MISS') desc) rn from deliveries)
  4   where rn = 1;

MODELNUMBER          RELEASENUMBER        DELIVERY DELIVE CUSTOMER
-------------------- -------------------- -------- ------ ----------
AB-CA12W115918010925 E3087TKS11           20180401 13029  Alpha
AB-CA13W115918080790 E0336TKS12           20180401 13032  RAILEX
AB-CA14W115918021400 E2555TKS13           20180401 64953  TRITON
AB-CA15W115918081536 E2555TKS14           20180401 64956  MAXED
AB-CA16W115918021398 E2555TKS15           20180401 65000  WILBURN
AB-CA17W115918081538 E2555TKS16           20180410 65011  Alpha
AB-CA19W115918081537 E2555TKS18           20180410 65015  RAILEX
AB-CA20W115918081535 E2555TKS19           20180410 65021  MAXED
AB-CA22W115918050925 E0226TKS21           20180410 65617  WILBURN
AB-CA23W115918050930 E0226TKS22           20180410 65618  MADDOx
AB-CA24W115918050931 E0226TKS23           20180410 65627  TRITON
AB-CA27W115918050934 E0226TKS26           20180410 65629  MAXED
AB-CA28W115918050937 E0226TKS27           20180410 65632  WILBURN
AB-CA28W115918050937 E0226TKS27           20180411 65635  MADDOx
AB-CA30W115918021394 E0226TKS29           20180412 65638  Alpha
AB-CA31W115918021395 E0226TKS30           20180413 65641  RAILEX
AB-CA32W115918021397 E0226TKS31           20180414 65643  TRITON
AB-CA33W115918021404 E0226TKS32           20180415 65646  MAXED
AB-CA34W115918021405 E0226TKS33           20180416 65648  WILBURN
AB-CA35W115918021410 E0226TKS34           20180417 65650  MADDOx
AB-CA36W115918021411 E0226TKS35           20180418 65653  Alpha
AB-CA37W115918081540 E0226TKS36           20180419 65656  RAILEX
AB-CA38W115918021251 E0336TKS37           20180420 163441 TRITON
AB-CA39W115918081303 E0336TKS38           20180421 163454 MAXED
AB-CA40W115918200424 E0336TKS39           20180422 163512 WILBURN
AB-CA41W115918021128 E0226TKS40           20180423 163521 Maddox
0
Roberto Madro R.Programmer AnalystAuthor Commented:
Ran the script against another database and all came out fine.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.