How to seperate and display the max value and neglect the lowest value?

Question

How to seperate and display the max value and neglect the lowest value?

raghuldrag

Hall of Fame

Points: 3160
More actions
January 8, 2014 at 11:48 pm

#280941

Dear 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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63421 More actions · Answer 1

How about a couple of things -- code we can read, and some sample data? (CREATE TABLE script(s) and INSERTs). And while I'm being demanding, how about an example of the expected output?

raghuldrag Hall of Fame Points: 3160 More actions · Answer 2

Hey Sri ,

Lets Consider table like

create table Bill_max

(

bill_no int,

cust_det int,

product varchar(20),

Price int,

bill_date datetime

)

insert into bill_max(bill_no, cust_det,product,prize,Bill_date) values('25','ram','Milk','25','01-apr-2013')

insert into bill_max(bill_no, cust_det,product,prize,Bill_date) values('25','ram','Perfume','150','01-apr-2013')

insert into bill_max(bill_no, cust_det,product,prize,Bill_date) values('26','sam','Fruits','50','01-apr-2013')

insert into bill_max(bill_no, cust_det,product,prize,Bill_date) values('26','sam','Books','500','01-apr-2013')

select * from bill_max

above details ll show here

now i wanna to display max sales for billed cust.(i.e)

bill_no Cust_det product Max_prize bill_date

25 ram perfume 150 01-apr-2013

26 sam Book 500 01-apr-2013

My expectation when the customer is billed many item on same bill_no but i wanna separate only highest value in that (i.e with out rank)

how to do that?

tripleAxe SSCertifiable Points: 5706 More actions · Answer 3

So how about.....

SELECT

bm.bill_no,

bm.cust_det,

bm.product,

bm.price,

bm.bill_date

FROM bill_max bm

INNER JOIN

(

SELECT MAX(price) [max_price], cust_det

FROM bill_max

GROUP BY cust_det

) AS max_price

ON max_price.max_price = bm.price

AND max_price.cust_det = bm.cust_det

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

raghuldrag (1/9/2014)
Hey Sri ,
Lets Consider table like
create table Bill_max
(
bill_no int,
cust_det int,
product varchar(20),
Price int,
bill_date datetime
)
insert into bill_max(bill_no, cust_det,product,prize,Bill_date) values('25','ram','Milk','25','01-apr-2013')
insert into bill_max(bill_no, cust_det,product,prize,Bill_date) values('25','ram','Perfume','150','01-apr-2013')
insert into bill_max(bill_no, cust_det,product,prize,Bill_date) values('26','sam','Fruits','50','01-apr-2013')
insert into bill_max(bill_no, cust_det,product,prize,Bill_date) values('26','sam','Books','500','01-apr-2013')
select * from bill_max
above details ll show here
now i wanna to display max sales for billed cust.(i.e)
bill_no Cust_det product Max_prize bill_date
25 ram perfume 150 01-apr-2013
26 sam Book 500 01-apr-2013
My expectation when the customer is billed many item on same bill_no but i wanna separate only highest value in that (i.e with out rank)
how to do that?

(i.e with out rank)

Why is that? I wouldn't use RANK() for this anyway, I'd use ROW_NUMBER().

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

David Burrows SSC Guru Points: 65138 More actions · Answer 5

;WITH a (bill_no,cust_det,product,Price,bill_date,rowid)

AS (SELECT bill_no,cust_det,product,Price,bill_date,ROW_NUMBER() OVER (PARTITION BY bill_no ORDER BY price DESC) FROM Bill_max)

SELECT bill_no,cust_det,product,Price,bill_date

FROM a

WHERE rowid = 1

ORDER BY bill_no ASC

Far away is close at hand in the images of elsewhere.
Anon.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

David Burrows (1/9/2014)
;WITH a (bill_no,cust_det,product,Price,bill_date,rowid) AS (SELECT bill_no,cust_det,product,Price,bill_date,ROW_NUMBER() OVER (PARTITION BY bill_no ORDER BY price DESC) FROM Bill_max) SELECT bill_no,cust_det,product,Price,bill_date FROM a WHERE rowid = 1 ORDER BY bill_no ASC

Exactly! :hehe:

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden