CODEWITHSUNDEEP
sqlsql-servermax
Dennyc
Dennyc

Reputation: 63

SQL query to get maximum value for each day

So I have a table that looks something like this:

enter image description here

Now, I want the max totalcst for both days, something like this:

enter image description here

I tried using different variations of max and the Row_number funtion but still can't seem to get the result I want. My query:

select date,pid,max(quan*cst), totalcst
from dbo.test1
group by date, pid

But this returns all the records. So if somebody can point me towards the right direction, that would be very helpful.

Thanks in advance!

Upvotes: 2

Views: 2678

Answers (2)

Lamak
Lamak

Reputation: 70638

ROW_NUMBER should work just fine:

WITH CTE AS
(
    SELECT  *,
            RN = ROW_NUMBER() OVER(PARTITION BY [date] ORDER BY totalcst)
    FROM dbo.YourTable
)
SELECT  [date],
        pid,
        totalcst
FROM CTE
WHERE RN = 1
;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Here is one simple way:

select t.*
from test1 t
where t.totalcst = (select max(t2.totalcst) from test1 t2 where t2.date = t.date);

This often has the best performance if you have an index on (date, totalcst). Of course, row_number()/rank() is also a very acceptable solution:

select t.*
from (select t.*, row_number() over (partition by date order by totalcst desc) as seqnum
      from test1
     ) t
where seqnum = 1;

Upvotes: 0

Related Questions