Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 686
  • Last Modified:

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

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
amgrobins
Asked:
amgrobins
  • 6
  • 5
  • 3
  • +1
1 Solution
 
CluskittCommented:
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
 
amgrobinsAuthor Commented:
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
 
Rajkumar GsSoftware EngineerCommented:
Can you elaborate your requirement ?

Raj
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
amgrobinsAuthor Commented:
I want every unique value from ONE column. but i also want the data from the other columns for each unique row.
0
 
Rajkumar GsSoftware EngineerCommented:
In your expected result, the part number 'AB100/4' is contains in other record. ?
What do you mean by unique partno ?

Raj
0
 
amgrobinsAuthor Commented:
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
 
CluskittCommented:
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
 
CluskittCommented:
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
 
Rajkumar GsSoftware EngineerCommented:
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
 
Rajkumar GsSoftware EngineerCommented:
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
 
awking00Commented:
RajkumarGS' query should work fine as long as you have SQL Server 2005 or later.
0
 
Rajkumar GsSoftware EngineerCommented:
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
 
amgrobinsAuthor Commented:
A+
0
 
Rajkumar GsSoftware EngineerCommented:
Nice to see you got the solution.

Thanks for the points
Raj
0
 
amgrobinsAuthor Commented:
:)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 6
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now