Solved

DISTINCT query alternative - retrieve one unique column and the other columns for that row

Posted on 2010-08-19
15
623 Views
Last Modified: 2012-05-10
Hello,

I have a database with a table as follows:

Id - int
poId - int
partNo - varchar(50)
description - text
quantity -int

example data:

1 - 1 - AB100/4 - A screw - 100
2 - 1 - AB100/5 - A bolt - 200
3 - 2 - AB100/4 - A screw - 150

I want to construct a query which will retrieve full rows that have a unique partNo.

So for example I would like to return the following data:

2 - 1 - AB100/5 - A bolt - 200
3 - 2 - AB100/4 - A screw - 150


How can I achieve this? I have tried the following but it doesnt work because it does a DISTINCT on all of the columns selected and therefore displays every row.

SELECT DISTINCT partNo, Id, poId, description, quantity FROM poLineItems
Order By Id DESC


Any ideas?
0
Comment
Question by:amgrobins
  • 6
  • 5
  • 3
  • +1
15 Comments
 
LVL 18

Expert Comment

by:Cluskitt
Comment Utility
select partNo, Id, poId, description, quantity FROM poLineItems as p1 inner join (select distinct partNo from poLineItems) as p2 on p1.Id=p2.Id
Order By Id DESC
0
 

Author Comment

by:amgrobins
Comment Utility
i got the following error:


Msg 207, Level 16, State 1, Line 1
Invalid column name 'Id'.
Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'partNo'.
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
Comment Utility
Can you elaborate your requirement ?

Raj
0
 

Author Comment

by:amgrobins
Comment Utility
I want every unique value from ONE column. but i also want the data from the other columns for each unique row.
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
Comment Utility
In your expected result, the part number 'AB100/4' is contains in other record. ?
What do you mean by unique partno ?

Raj
0
 

Author Comment

by:amgrobins
Comment Utility
AB100/4 may occur 100 times. In my result set I only want the most recent entry of AB100/4 along with the quantity, description, poId
0
 
LVL 18

Expert Comment

by:Cluskitt
Comment Utility
select p1.partNo, p1.Id, poId, description, SUM(quantity) FROM poLineItems as p1 inner join (select max(id) as id2, partNo from poLineItems group by partNo) as p2 on p1.Id=p2.Id2
Group by p1.partNo, p1.Id, poId, description
Order By Id DESC
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 18

Expert Comment

by:Cluskitt
Comment Utility
That will give the sum of all the items you have... if you only want the last value, remove sum, and the group by line in the end.
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
Comment Utility
Replace tablename with yours
Raj
select * from

(

select *, row_number() over(partition by partNo order by id desc) rowno

from #table

) a

where a.rowno = 1

Open in new window

0
 
LVL 23

Accepted Solution

by:
Rajkumar Gs earned 500 total points
Comment Utility
Updated my query little bit with necessary columns and sort by id
Raj
select Id, poId, partNo, description, quantity 

from

(

	select *, row_number() over(partition by partNo order by id desc) rowno

	from #table

) a

where a.rowno = 1

order by id

Open in new window

0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
RajkumarGS' query should work fine as long as you have SQL Server 2005 or later.
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
Comment Utility
awking is right. If you have SQL Server below v2005, try this query

Raj
select a.* from #table a

inner join

(

select partNo, max(id) as id from #table

	group by partNo

) b on a.id = b.id

Open in new window

0
 

Author Closing Comment

by:amgrobins
Comment Utility
A+
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
Comment Utility
Nice to see you got the solution.

Thanks for the points
Raj
0
 

Author Comment

by:amgrobins
Comment Utility
:)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now