Group by in SQL store procedure

Question

0.00/5 (No votes)

See more:

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 21-Sep-16 22:17pm

kashifjaat

Updated 21-Sep-16 22:43pm

v2

Add a Solution

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

Add a Solution

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

OriginalGriff · Accepted Answer · 2016-09-21T22:43:00

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.

Group by in SQL store procedure

1 solution

Solution 1

Add your solution here

Preview 0

Group by in SQL store procedure

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us