We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

SQL SELECT DISTINCT on only one column

Medium Priority
1,637 Views
Last Modified: 2012-05-06
I should be able to figure this out, but I'm being lazy....

How do I use the DISTINCT but on only one column?

Given the table:
Item Bid
1 100.00
2 100.00
3 100.00
1 150.00
3 125.00
1 175.00

What query do I use to get back the result set:
Item Bid
1 175.00
2 100.00
3 125.00

Obviously the attached query isn't working, because each ROW is DISTINCT, so I'm getting back everything.

Thanks!!
SELECT DISTINCT Item, Bid from dbo.ItemBids ORDER BY Bid DESC

Open in new window

Comment
Watch Question

Commented:
SELECT DISTINCT [Bid]
FROM yourTable
Commented:
if you want to get only one Item number then you should use a GROUP BY clause... here you should decide which Item number you will get? the min or max....

SELECT MIN(Item), Bid
FROM yourTable
GROUP BY Bid

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Commented:
SELECT Item, MAX(Bid) as BID
from dbo.ItemBids
GROUP BY Item
ORDER BY Item

Author

Commented:
I'll test these out and be right back.

Author

Commented:
tigin - you were on the right track, but I wanted the max bid for each item, not the mininum item for each bid.

reb- you finished it up!

Thanks guys!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.