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

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.

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.

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;

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

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

Now its throwing me ORA-01850 hour must be between 0 and 23.

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

>> 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

@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.

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

Might be a data issue?

From the data you posted in ID: 42542756, what would you expect as output?

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.

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.

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

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.

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

Select allOpen 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;

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;
*/

Select allOpen 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.

Select allOpen 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.

Select allOpen in new window

His added an extra row.  Is that the correct results from the sample data provided?

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

>>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.

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.

>>or an excel spreadsheet will do.

I prefer a TXT file.  As seen above:  Excel can "change" the data.

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

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?

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

Ran the script against another database and all came out fine.