asked on

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?

Cluskitt

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

amgrobins

ASKER

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'.

Rajkumar Gs

Can you elaborate your requirement ?

Raj

amgrobins

ASKER

I want every unique value from ONE column. but i also want the data from the other columns for each unique row.

Rajkumar Gs

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

Raj

amgrobins

ASKER

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

Cluskitt

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

Cluskitt

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.

Rajkumar Gs

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

ASKER CERTIFIED SOLUTION

Rajkumar Gs

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

awking00

RajkumarGS' query should work fine as long as you have SQL Server 2005 or later.

Rajkumar Gs

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

amgrobins

ASKER

A+

Rajkumar Gs

Nice to see you got the solution.

Thanks for the points
Raj

amgrobins

ASKER