Group by after Inner Join - 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 2005 Forums

Transact-SQL (2005)

Group by after Inner Join

Author

Topic

Jarhead104
Starting Member

10 Posts

Posted - 2010-01-18 : 09:20:48

Hey!

Table A:
id username
1 bla
2 blo
3 blub

Table B:
id users_id date
1 1 2009
2 1 2010
3 1 2011
4 2 2009
5 3 2006
6 3 2007
7 3 2008
8 3 2009
9 3 2010
10 3 2010

I now want to select id, username of table A and ONLY ONE value of date of table B. Result should be:

id username date
1 bla 2009
2 blo 2009
3 blub 2006

What I have:
Select a.id, a.username, b.date from a inner join b on a.id = b.users_id

But when i add a group by a.id argument i get an exception.
It says that b.date is not part of the group by argument and that this does not work...but i do not want to group by b.date...

Best regards,
Thomas

Kristen
Test

22859 Posts

Posted - 2010-01-18 : 09:31:07

"ONLY ONE value of date of table B"

How did you decide WHICH value from table B you would display? Is it the MINimum value??

Jarhead104
Starting Member

10 Posts

Posted - 2010-01-18 : 09:57:07

Yeah, the minimum value or the first value...actually that's not so important due the fact that "date" is actually a time and the differences between the times are only some milliseconds and are not important for this query.

So i dont care which value...im fine with the easiest one ;)

Kristen
Test

22859 Posts

Posted - 2010-01-18 : 10:23:07

[code]
Select a.id, a.username, MIN(b.date)
from a
inner join b
on a.id = b.users_id
GROUP BY a.id, a.username
[/code]

Jarhead104
Starting Member

10 Posts

Posted - 2010-01-18 : 10:55:47

Works great!

Thank you, Kristen!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources