Trouble With Distinct

[DonP]

I know that "distinct" effects the whole string of fields after it, but in this case, I want "distinct" to apply only to the ul.CompID field and not the others but I also want the latest ul.UpdateDate entry. Can someone help or at least tell me if this is possible or do I need more scripting outside the select statement to do it?

[tt]select distinct ul.CompID as PropertyID, Email, RealName, c.CompBuildingName
as BuildingName, ul.UpdateDate as UpdateDate from UpdateLog ul
left join users on UserID=AcctID
left join Comp c on c.CompID=ul.CompID
where UserID = 100 and ul.CompID <> 0
order by c.CompBuildingName, ul.UpdateDate desc[/tt] Don
don@pc-homepage.com

http://www.pc-homepage.com/

Experienced in HTML, Perl, VBScript, PWS, IIS and Apache. Run OS/2 Warp 4, BeOS v5 and Windows NT/2000 (only when I have to!)

 

[Turkbear]

Hi,
Just a quick thought..

Can you make a temporary table with distinct values for CompID first then use that in your code as one of the joined tables?

 

[DonP]

I thought of that but couldn't seem to get any different results. Did you mean a temporary &quot;table&quot; though as you said or did you mean to say temporary &quot;recordset&quot;? I tried it as a recordset, not as a table since I would rather prefer to avoid the extra overhead of temporary tables if there is another way to do it. Don
don@pc-homepage.com

http://www.pc-homepage.com/

Experienced in HTML, Perl, VBScript, PWS, IIS and Apache. Run OS/2 Warp 4, BeOS v5 and Windows NT/2000 (only when I have to!)

 

[Turkbear]

Hi,
I actually meant temporary table; In Oracle I would have used a view, but you seem to be in SqlServer or Access so I don't know the actual equivalent to views..

Substitute the new table ( or view equivalent ) :

select distinct nc.CompID as PropertyID, ul.Email, ul.RealName, c.CompBuildingName
as BuildingName, ul.UpdateDate as UpdateDate from
NewComp nc, UpdateLog ul

need some join logic here for the NewComp I think

left join users on UserID=AcctID
left join Comp c on c.CompID=nc.CompID
where UserID = 100 and ul.CompID <> 0
order by c.CompBuildingName, ul.UpdateDate desc

NOTE: I do not use your database's Sql so this may be only an approximation...

 

[DonP]

Yes, this is SQL Server (I forgot to specify). Your select statement makes sense but - just to be clear - is this supposing that I've saved the distinct CompID values to the NewComp table first? Assuming that is the intent, I created and manually populated a simple table so that I can test the statement. There is an error so far but I'm working on it. Don
don@pc-homepage.com

http://www.pc-homepage.com/

Experienced in HTML, Perl, VBScript, PWS, IIS and Apache. Run OS/2 Warp 4, BeOS v5 and Windows NT/2000 (only when I have to!)

 

[Turkbear]

Hi,
Yes..pre-populate the table with the distinct values then use that table in the select statements..

( That's why I like Oracle's views, they are always updated
with the latest data from the underlying table(s))