Help for rookie on selecting max bids

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help for rookie on selecting max bids

Author

Topic

ctcqc
Starting Member

7 Posts

Posted - 2011-09-14 : 22:36:17

I have a simple charity auction form that captures First, Last, and Bid. I am retrieving the top three bids for a table using the following:

SELECT * FROM Bids ORDER BY Bid DESC LIMIT 3

What I'm trying to do is avoid returning two bids for the same bidder. In other words if the current top three are:
Bob Smith 50
Joe Jones 30
Jim Wilson 20

And Joe Jones places another bid for 60, I'm currently returning:
Joe Jones 60
Bob Smith 50
Joe Jones 30

I obviously want to ignore the lower Joe Jones bid.

Easy fix?

Thanks

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2011-09-14 : 22:45:49

select name, max(bid) from your_table group by name order by max(bid) desc

elsasoft.org

ctcqc
Starting Member

7 Posts

Posted - 2011-09-14 : 23:00:08

Thank you!

ctcqc
Starting Member

7 Posts

Posted - 2011-09-16 : 13:12:24

quote:
Originally posted by jezemine

select name, max(bid) from your_table group by name order by max(bid) desc

elsasoft.org

Oops. Said "thanks" to soon. Still doesn't seem to be working. Higher bids from same person still not replacing the lower bid in the table return. Thoughts?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-16 : 13:19:45

[code]
INSERT INTO Return
SELECT TOP 3 Name,bid
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY name ORDER BY bid DESC) AS Rn,Name,bid
FROM table
)t
WHERE Rn=1
ORDER BY bid DESC
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-09-16 : 15:19:29

quote:
Originally posted by ctcqc
Oops. Said "thanks" to soon. Still doesn't seem to be working. Higher bids from same person still not replacing the lower bid in the table return. Thoughts?

What does that mean? In the result set? In the table?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

ctcqc
Starting Member

7 Posts

Posted - 2011-09-18 : 11:00:34

quote:
Originally posted by X002548

quote:
Originally posted by ctcqc
Oops. Said "thanks" to soon. Still doesn't seem to be working. Higher bids from same person still not replacing the lower bid in the table return. Thoughts?

What does that mean? In the result set? In the table?

In the result set.

ctcqc
Starting Member

7 Posts

Posted - 2011-09-18 : 11:01:20

quote:
Originally posted by visakh16

INSERT INTO Return
SELECT TOP 3 Name,bid
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY name ORDER BY bid DESC) AS Rn,Name,bid
FROM table
)t
WHERE Rn=1
ORDER BY bid DESC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks, but need a little more step-by-step to try that.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-18 : 11:15:23

hmm...what does that mean?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ctcqc
Starting Member

7 Posts

Posted - 2011-09-18 : 12:18:50

quote:
Originally posted by visakh16

hmm...what does that mean?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Your suggesting edits to both the insert script and the select script, right? Any variable in what you suggest I need to fill in with my info?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-18 : 13:00:02

quote:
Originally posted by ctcqc

quote:
Originally posted by visakh16

hmm...what does that mean?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Your suggesting edits to both the insert script and the select script, right? Any variable in what you suggest I need to fill in with my info?

nope
based on your current requirement and output i think you can use that query directly

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ctcqc
Starting Member

7 Posts

Posted - 2011-09-20 : 21:32:04

quote:
Originally posted by visakh16

quote:
Originally posted by ctcqc

quote:
Originally posted by visakh16

hmm...what does that mean?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Your suggesting edits to both the insert script and the select script, right? Any variable in what you suggest I need to fill in with my info?

nope
based on your current requirement and output i think you can use that query directly

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Still having issues. Here is the full query line in my select script:

$select = mysql_query("SELECT First,Last,Bid FROM Bids GROUP BY First,Last ORDER BY MAX(Bid) DESC LIMIT 3") or die(mysql_error());

What do I need to do the edit this? Pasting in your code above results in errors.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-20 : 22:06:01

oh...so you're in MySQL. This is MS SQL Server forum so solutions given here are SQL Server specific. you need to post it in some MySQL forums to get specific syntax help. Please try your luck at www.dbforums.com

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources