Oracle SQL print first and last entry in a table?

Question

So I need to print the oldest and youngest result in a table my code is

SELECT client.clientfirstname, client.clientlastname, client.clientdob
FROM client
order by (client.clientdob) DESC
FETCH first 1 row only


SELECT client.clientfirstname, client.clientlastname, client.clientdob
FROM client
order by (client.clientdob) ASC
FETCH first 1 ROW ONLY;

these 2 by themselves will return the right value but I cant get them both to run at once, it seems I cant use a Union with this setup, if possible a single select statement where I can get both the first and last would be even better. I am also using SQL developer if there is way to get both of these to show in the same SQL query result window that would be fine.

Did you tried UNION ALL solution? – Soumendra Mishra Sep 06 '20 at 08:30 — Soumendra Mishra, Sep 06 '20 at 08:30

Fahmi · Accepted Answer · 2020-09-06T08:09:05.903

1

You can try the below -

SELECT client.clientfirstname, client.clientlastname, client.clientdob
FROM client where 
client.clientdob=(select max(client.clientdob) from client)
or 
client.clientdob=(select min(client.clientdob) from client)

edited Sep 06 '20 at 08:09

answered Sep 06 '20 at 07:56

Fahmi

37,315
5
22
31

sql dev gives me a "SQL command not properly ended" error when I try that. same with union. – Comradedrago Sep 06 '20 at 08:03
@Comradedrago, I've updated the answer - you can take a look – Fahmi Sep 06 '20 at 08:09
This works mostly only issue is that multiple people share a DOB value for MAX and MIN so that's why I needed to limit it to 1 max and 1 min. Is that possible with the min and max options? – Comradedrago Sep 06 '20 at 08:23
@Comradedrago, you can try Soumendra Mishra's solution if that works for your case – Fahmi Sep 06 '20 at 08:25
1

yours worked great I just added a Fetch first 2 row only and that fixed the issue, thank you for the help. – Comradedrago Sep 06 '20 at 08:27
@Comradedrago, great to hear that it helped u :) – Fahmi Sep 06 '20 at 08:27

score 0 · Answer 2 · answered Sep 06 '20 at 08:22

You can use UNION ALL like this below:

select c1.* from (select client.clientfirstname, client.clientlastname, client.clientdob, row_number() over (order by client.clientdob ASC) as rn FROM client) c1 where c1.rn =1
union all
select c2.* from (select client.clientfirstname, client.clientlastname, client.clientdob, row_number() over (order by client.clientdob DESC) as rn FROM client) c2 where c2.rn =1

score 0 · Answer 3 · answered Sep 06 '20 at 08:24

We can use analyitical function row_number() to get the desired result,

select *
  from
(
select cl.clientfirstname
     , cl.clientlastname
     , cl.clientdob 
     , row_number() over (order by cl.clientdob desc) max_rn
     , row_number() over (order by cl.clientdob asc) min_rn
FROM client cl
) t
where (max_rn = 1 or min_rn = 1)

Oracle SQL print first and last entry in a table?

3 Answers3