October 16, 2007 at 4:21 am
Hi All,
I currently have the following SQL Statement:
select c.Id, c.name, c.active, a.name as attribute
from component as c join attribute as a on c.attributeid = a.id
order by c.name asc
this gives me data in the form:
183Communal Enclosure 1Cleaning
150Composite 1Doors Generally
21Composite 1 Windows
33Composite 1 Doors Generally
36Door Frame 1Doors Generally
46Door Frame 1Doors Communal
154Door Frame 1Doors Generally
The problem is that I have duplicates of columns c.name and a.name with different Id's
I want to list distinct based on these 2 columns and furthermore want only the row with the highest id on duplicates returned. How should the Select statement be changed? It can be a stored proc.
Thanks.
October 16, 2007 at 4:59 am
Are you looking at something like
select c.Id,
c.[name],
c.active,
a.[name] as attribute
from
component as c
join
attribute as a on c.attributeid = a.id
inner join
(select max(c.id) as cid ,c.[name],a.[name] e from component as c
join attribute as a on c.attributeid = a.id group by c.name, a.name) a1 ON a1.cid = c.id
order by c.name asc
Prasad Bhogadi
www.inforaise.com
October 16, 2007 at 5:12 am
Thank you, that seems to have done it.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply