aggregate queries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

aggregate queries

Author

Topic

hansalas
Starting Member

14 Posts

Posted - 2006-08-23 : 05:53:12

Table One.....................Table Two
id--|-type....................type---|typeDescr
1---|-100.....................100----|alpha
1---|-200.....................200----|bravo
1---|-101.....................400----|gamma
2---|-900
3---|-400
3---|-100

Here is an example tables...

select id,max(type)
from tableOne
group by id

This is wat i am doing now... But I wanna also display the type description for the max type...

select id,max(type).max(type)'s description

Not to complicate things at this time, there is also another table 3 which i also wanna use to display the id's desciption in the above query...

Anyone knoes how can I execute this queery...

Thanks in advance,fellars.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-23 : 05:56:41

[code]SELECT t1.ID,
t1.Type,
t2.TypeDescr,
t3.IdDescr
FROM (
SELECT ID,
MAX(Type) Type
FROM TableOne
GROUP BY ID
) t1
LEFT JOIN TableTwo t2 ON t2.Type = t1.Type
LEFT JOIN TableThree t3 ON t3.ID = t1.ID[/code]
Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources