How to pick the first entry out of all the list

Question

In this test table, with below data. Even though how many entries are there, need to pick the first valid record for that day. Also, there is chance that for that day where rank no might increase.

A	B	STATUS	LOAD_DATE	RANKNO
SAMPLE	SAMPLE	VALID	10-Jan-22	3
SAMPLE	SAMPLE	INVALID	10-Jan-22	5
SAMPLE	SAMPLE	VALID	10-Jan-22	6 **
SAMPLE	SAMPLE	VALID	1-Jan-22	10
SAMPLE	SAMPLE	INVALID	1-Jan-22	12
SAMPLE	SAMPLE	INVALID	1-Jan-22	12

For Jan 10, 2022 the expected output is, line:

A	B	STATUS	LOAD_DATE	RANKNO
SAMPLE	SAMPLE	VALID	10-Jan-22	6 **

We can do select * from test where rankno = 6 (this is for 10-jan-22) but there might be chance that for 11-jan-22 there will be only 2 records, we need to pick record with highest rankno

if I query with Load_date, it should fetch valid record with highest rankno.

SELECT A, B, Status, load_date 
FROM   test
WHERE  status = 'VALID' 
AND    ....

or can we?

SELECT max(rankno) 
FROM   test 
WHERE  load_date = '10-JAN-22'

score 1 · Answer 1 · 2022-04-11T19:32:01.403

1

The following query will return the row in blue.
FETCH FIRST 1 ROWS ONLY returns only the first row according to the ORDER BY specified.

SELECT 
  A, B, Status, 
  load_date,
  RANKNO,
  ROWNUM
FROM test 
WHERE status = 'VALID' 
ORDER BY 
  load_date ASC,
  RANKNO DESC
fetch first 1 rows only;

See https://dbfiddle.uk/?rdbms=oracle_18&fiddle=793f06cc4a3d7ea6e978eb683ce3d095

edited Apr 11 '22 at 19:32

answered Apr 11 '22 at 16:58

it says command is not properly ended at limit 1 – Karuna Apr 11 '22 at 17:15
Added `;` please try again – Apr 11 '22 at 17:17
Yes, have done. Still same error. So this will work without using max or something? @kendle – Karuna Apr 11 '22 at 17:20
Sorry I forgot in oracle we have to use `ROWNUM = 1` and not LIMIT – Apr 11 '22 at 17:27
It is picking Low rankno. Rankno 3 is getting picked, eventhough we sort by descending order. – Karuna Apr 11 '22 at 17:37
You need to order first, than in an outer `SELECT` limit with `ROWNUM`. – D. Mika Apr 11 '22 at 18:03
Yes, even after that, low rankno is getting picked. – Karuna Apr 11 '22 at 18:10
@Karuna - Are you trying to retrieve a single row for a *single* date - or multiple dates? – SOS Apr 11 '22 at 19:01
1

Got it `FETCH FIRST 1 ROWS ONLY` I was sure there was a way. Oracle couldn't be lacking such a basic function as LIMIT, even if they have there own syntax! – Apr 11 '22 at 19:34
@SOS: For single date – Karuna Apr 12 '22 at 08:15

score 1 · Accepted Answer · answered Apr 11 '22 at 19:26

Here we use RANK() in a sub query to get the wanted row in the outer query.

create table test (
A varchar(10),
B varchar(10),
STATUS VARCHAR (10),
LOAD_DATE date,
RANKNO int);

✓

INSERT INTO test (A,B,STATUS,LOAD_DATE,RANKNO)
SELECT 'SAMPLE','SAMPLE','VALID',TO_DATE('2022-01-01', 'yyyy-mm-dd'),3 FROM DUAL UNION ALL
SELECT 'SAMPLE','SAMPLE','INVALID',TO_DATE('2022-01-01', 'yyyy-mm-dd'),5 FROM DUAL UNION ALL
SELECT 'SAMPLE','SAMPLE','VALID',TO_DATE('2022-01-01', 'yyyy-mm-dd') ,6 FROM DUAL UNION ALL
SELECT 'SAMPLE','SAMPLE','VALID',TO_DATE('2022-01-10', 'yyyy-mm-dd'),10 FROM DUAL UNION ALL
SELECT 'SAMPLE','SAMPLE','INVALID',TO_DATE('2022-01-10', 'yyyy-mm-dd'),12 FROM DUAL UNION ALL
SELECT 'SAMPLE','SAMPLE','INVALID',TO_DATE('2022-01-10', 'yyyy-mm-dd'),14 FROM DUAL

6 rows affected

SELECT * FROM 
(SELECT 
  A, B, Status, 
  load_date ,
  ROWNUM,
  RANKNO,
  RANK() OVER (
     ORDER BY load_date ASC,
       RANKNO DESC) rnk
FROM test 
WHERE 
  status = 'VALID' 
  --AND ROWNUM = 1
ORDER BY 
  load_date ASC,
  RANKNO DESC)s
WHERE rnk = 1;

A      | B      | STATUS | LOAD_DATE | ROWNUM | RANKNO | RNK
:----- | :----- | :----- | :-------- | -----: | -----: | --:
SAMPLE | SAMPLE | VALID  | 01-JAN-22 |      2 |      6 |   1

db<>fiddle here

Do not use `RANK` as it can return multiple rows when there are ties for first. Use `ROW_NUMBER`. — MT0, Apr 11 '22 at 21:04

How to pick the first entry out of all the list

2 Answers2