[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL SELECT DISTINCT on only one column

Posted on 2009-02-09
5
Medium Priority
?
1,600 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

0
Comment
Question by:dban00b
  • 2
  • 2
5 Comments
 
LVL 26

Expert Comment

by:tigin44
ID: 23595566
SELECT DISTINCT [Bid]
FROM yourTable
0
 
LVL 26

Assisted Solution

by:tigin44
tigin44 earned 400 total points
ID: 23595591
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

0
 
LVL 25

Accepted Solution

by:
reb73 earned 1600 total points
ID: 23595624
SELECT Item, MAX(Bid) as BID
from dbo.ItemBids
GROUP BY Item
ORDER BY Item

0
 
LVL 1

Author Comment

by:dban00b
ID: 23595727
I'll test these out and be right back.
0
 
LVL 1

Author Comment

by:dban00b
ID: 23595850
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!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question