Click here to Skip to main content
15,354,542 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I want only those rows where AucNumber and highest bid price with datetime what will be changed in my Store rocedure, I want only AucNumber where price is highest

I want result like this
1 LE-16 2500 500000000 2016-09-22 12:13:13.000
2 LE-16 2000 60000 2016-09-20 15:40:16.000
50000 LE-16 2000 NULL NULL
800000 LE-16 2000 NULL NULL


SELECT AuctionsSelectedNumber.AucNumber,
       AuctionsSelectedNumber.Auc_Series,AuctionsSelectedNumber.Price,
	   AuctionsSelectedNumber.BiddingPrice,MAX(AuctionsSelectedNumber.BiddingDateTime)
FROM   AuctionsSelectedNumber
GROUP  BY AucNumber,IsNonAction,Auc_Series,Price,BiddingPrice


RESULT

AucNumber Auc_Series	Price	BiddingPrice	(No column name)
1	LE-16	1500	800000	2016-09-20 20:59:49.000
1	LE-16	2500	NULL	NULL
1	LE-16	2500	200000	NULL
1	LE-16	2500	90000000	NULL
1	LE-16	2500	500000000	2016-09-22 12:13:13.000
2	LE-16	2000	60000	2016-09-20 15:40:16.000
2	LE-16	3000	NULL	NULL
50000	LE-16	2000	NULL	NULL
800000	LE-16	2000	NULL	NULL


What I have tried:

I want only those AucNumber where price is highest
Posted
Updated 21-Sep-16 22:43pm
v2
Comments
OriginalGriff 22-Sep-16 4:21am
   
Show us what you want to get, as well as what you do get - it isn't obvious from that what you expect to SELECT.
Use the "Improve question" widget to edit your question and provide better information.
kashifjaat 22-Sep-16 4:25am
   
I want to get

1 LE-16 2500 500000000 2016-09-22 12:13:13.000
2 LE-16 3000 NULL NULL
50000 LE-16 2000 NULL NULL
800000 LE-16 2000 NULL NULL
OriginalGriff 22-Sep-16 4:35am
   
And what does the input data look like?
It looks like you have two completely different output formats...
OriginalGriff 22-Sep-16 4:36am
   
Ignore that - I assume that 50000 and 800000 are auction numbers.
kashifjaat 22-Sep-16 5:01am
   
Yes Dear 50000 and 800000 are auctions number
kashifjaat 22-Sep-16 5:02am
   
I want only auctions number where bid price is highest or DateTime is highest

1 solution

Start by looking at the GROUP BY - you have far, far too many clauses in there, which is why you get more rows than you want. (This may help you understand why: SQL GROUP By and the "Column 'name' is invalid in the select list because..." error[^])

So start with a simpler GROUP By:
SELECT AucNumber, MAX(BiddingPrice) AS MaxBP
FROM AuctionsSelectedNumber
GROUP BY AucNumber
That won't return you all the info you want, but it should bring it down the just the rows you want.
Then, use a JOIN to combine that with the rest if the data:
SQL
SELECT a.* FROM AuctionsSelectedNumber
JOIN (SELECT AucNumber, MAX(BiddingPrice) AS MaxBP
      FROM AuctionsSelectedNumber
      GROUP BY AucNumber) b
ON a.AucNumber = b.AucNumber AND a.MaxBP = b.MaxBP

I don't have your test data, so I can't check it - but that should do what you want.
If it doesn't, show your input and output data, and explain where the output is wrong.
   
Comments
kashifjaat 22-Sep-16 5:08am
   
Thanks Its Work
OriginalGriff 22-Sep-16 5:21am
   
You're welcome!
Maciej Los 22-Sep-16 11:52am
   
I have no idea who voted 2... My 5!
kashifjaat 23-Sep-16 0:58am
   
hahhaha... How I can Change now
Maciej Los 23-Sep-16 3:03am
   
Simple... Use voting system - click on the star (where 1 star is the lowest and 5 stars is the highest rate) on the right-top corner of answer. 3 stars means - neutral vote.
kashifjaat 30-Sep-16 1:45am
   
Oky Dear
kashifjaat 13-Dec-16 3:10am
   
How I can make this Query using LINQ in c# Using Entity Frame Work.??

SELECT a.* FROM AuctionsSelectedNumber
JOIN (SELECT AucNumber, MAX(BiddingPrice) AS MaxBP
FROM AuctionsSelectedNumber
GROUP BY AucNumber) b
ON a.AucNumber = b.AucNumber AND a.MaxBP = b.MaxBP

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900