How to seperate and display the max value ?

Rocky Rocky

12y1.6k

Hi Friends,

i ve the scenario like

The list of customer's was billed on 01-apr-2011

custname products value bill_date bill_no
ram Milk 25 01-apr-2011 25
ram Perfume 225 01-apr-2011 25
sam Egg 50 01-apr-2011 26
sam Medicine 125 01-apr-2011 26

now i wanna to extract the maximum billed value from above and display on my result like

custName products Max_sales billed billno
ram Perfume 225 01-apr-2011 25
sam Medicine 125 01-apr-2011 26

how to write code for these scenario?
do the need full

thanks

rocky

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Khan Abrar Ahmed12y ago
Hi you can use below code
DECLARE @t AS TABLE(custname VARCHAR(6),products VARCHAR(255), VALUE INT,bill_date DATE, bill_no INT)

INSERT INTO @t VALUES
('ram'   ,                  'Milk' ,          25 ,                 '01-apr-2011' ,           25),
('ram',                    'Perfume'   ,       225   ,        '01-apr-2011' ,           25),
('sam' ,                     'Egg',           50 ,               '01-apr-2011' ,          26),
('sam' ,                  'Medicine'    ,     125 ,          '01-apr-2011' ,         26)

select bill_no, products, VALUE as SALES, bill_date, custname
from (select *, row_number() over(partition by bill_no order by VALUE desc) RowNumber FROM @t) t
WHERE RowNumber=1
Hanook12y ago
select BillNo, products, Price as SALES, Billed_date, cust_det
from (select *, row_number() over(partition by Billno order by price desc) RN FROM TableName) t
WHERE RN=1
Rocky Rocky12y ago
Above My scenario has

Bill no products price Billed_date cust_det

25 Milk 25 01-Apr-2013 ram

Perfume 225   01-Apr-2013 ram

26 EGG 50    01-Apr-2013    sam

     Medicine 125   01-Apr-2013   sam

like lacks of records are there.

My expecting O/p is:

Bill no products SALES Billed_date cust_det

25 Perfume 225   01-Apr-2013 ram

26 Medicine 125   01-Apr-2013   sam

Here i wanna display the maximum amount product only display on it to neglect other products how to that?
Kiran Kumar Talikoti12y ago
Hi Rocky ,
Check this Query Hope this Helps you

select top 2 custname,products,value as Max_Sales,bill_date as Billed,bill_no as BillNo
from customer order by value desc ;
Rocky Rocky12y ago
Thanks for ur reply But i need only to display the maximum value of each bills.
Masoud DaneshPour12y ago
you can do something like this :

Select Top 2 * from YourTableName Order By Max_sales desc

the "top 2" guy returns the first 2 row that has max value after ordering