• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 684
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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