0

I want to fetch latest 3 different records from history table, below is my query.

select distinct(account_id)account_id,
       creation_date,
       category
from   account_history
where  account_id in('2345','3467','7896')
order by creation_date desc fetch first 3 rows only;

output: I am getting:

account_id creation_date category
2345        27-jan-22     p
3467        26-jan-22     p
3467        25-jan-22    p

required output:

account_id creation_date category
2345        27-jan-22     p
3467        26-jan-22     p
7896        24-jan-22    p
MT0
  • 143,790
  • 11
  • 59
  • 117
kumar
  • 37
  • 7
  • Use `DISTINCT` (or `UNIQUE`) in the `SELECT` clause to remove duplicates and, from Oracle 12, append `FETCH FIRST 3 ROWS ONLY` to the query to limit it to 3 rows. – MT0 Jan 27 '22 at 09:46
  • since im using fetch first 3 rows only after order by creation_date desc, it is showing same record in 3rd row not three different records. – kumar Jan 27 '22 at 10:12
  • Please [edit] the question with a [MRE] including: the `CREATE TABLE` statement for the table; the `INSERT` statement for some sample data; the output from **YOUR** query for that sample data; the issues/errors with that output; and the expected output. If you only give us a query and an ambiguous description of the problem then you may not get the answer you expect but if you give us all the information then we can give you an accurate answer. – MT0 Jan 27 '22 at 10:17
  • As I said, in the first comment, if you use `DISTINCT` (or `UNIQUE`) then you will not get duplicate rows. However, you probably need to clarify what you mean by "3 different records" as that is an ambiguous statement if you do not mean "rows" by "records" and instead mean something like "account_id"s. If it is the latter then you are asking for the "latest row for each account_ids" rather than the "latest 3 unique rows over any account_id". – MT0 Jan 27 '22 at 10:20
  • i inserted the required output and the outut im getting . i need the three account id s with latest date – kumar Jan 27 '22 at 13:44
  • I've updated the tags and changed the duplicates to point at the posts with answers to your problem. – MT0 Jan 27 '22 at 13:53

0 Answers0