Solved

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

Posted on 2010-08-19
15
669 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 3
  • +1
15 Comments
 
LVL 18

Expert Comment

by:Cluskitt
ID: 33474823
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
ID: 33474849
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
ID: 33474897
Can you elaborate your requirement ?

Raj
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:amgrobins
ID: 33474907
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
ID: 33474911
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
ID: 33474928
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
ID: 33474954
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
 
LVL 18

Expert Comment

by:Cluskitt
ID: 33474962
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
ID: 33474977
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
ID: 33475146
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 32

Expert Comment

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

Expert Comment

by:Rajkumar Gs
ID: 33492682
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
ID: 33839689
A+
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 33840690
Nice to see you got the solution.

Thanks for the points
Raj
0
 

Author Comment

by:amgrobins
ID: 33840695
:)
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
semaphore timeout period has expired 1 32
Sorting a SQL script 5 41
SQL Server Pivot 5 42
How to build a logic for passwords according to initials? 13 51
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

751 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