CODEWITHSUNDEEP
sqloracle11ggreatest-n-per-group
Skeeve
Skeeve

Reputation: 1255

Select row with the highest order value

For example, I have the table with the next columns: id, fk_id, date, order. How can I select something like this:

select 
  id
from cards
where fk_id = pId
and date = pDate;

but I need to get only the row with the highest order value. How can I do that? Thank you.

UPD1: First of all - thank you for your answers. Column names are completely different in the real table, it's just dummy names for question. Is there any way to do so that select by joining tha table with itself?

Upvotes: 0

Views: 159

Answers (4)

Full-Fledged
Full-Fledged

Reputation: 309

You can try something like this:

select 
id
from cards
where fk_id = pId
and date = pDate 
order by desc 
limit 1;

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

You would use the aggregating function KEEP DENSE_RANK FIRST for that:

select 
MIN(id) KEEP (DENSE_RANK FIRST ORDER BY "order" desc) 
from cards
where fk_id = pId
and "date" = pDate;

Upvotes: 3

tea2code
tea2code

Reputation: 1007

Something like this:

SELECT id
FROM cards
WHERE orderValue = (SELCT MAX(orderValue)
                    FROM cards
                    WHERE fk_id = pId
                    AND date = pDate)
AND fk_id = pId
AND date = pDate;

This is fairly efficient.

Upvotes: 1

Jindrich Vavruska
Jindrich Vavruska

Reputation: 830

First of all, do not use the words "order" and "date" for column names. Both are reserved words in SQL (ORDER BY ... clause, DATE is a data type). So in my example I am going to use "myorder" and "mydate" instead...

I assume your example as purely hypothetical because with the selected column names it would most likely not work on any SQL database, anyway.

In Oracle database you could try this:

select ID from 
( select ID, row_number() over (partition by ID order by MYORDER) as RN
  from CARDS where FK_ID = PID and MYDATE = PDATE)
where RN = 1

However, your select only has one column, ID. So if you simply do

select distinct ID from CARDS

you will get exactly the same result...

Upvotes: 1

Related Questions