Solved

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

Posted on 2010-08-19
15
664 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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…

749 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