query with group by - 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)

query with group by

Author

Topic

Lambik
Starting Member

13 Posts

Posted - 2010-05-06 : 15:21:20

Hello,

I have the following table:

name date location
John 20100202 Paris
John 20100204 Berlin
John 20100206 Amsterdam
Eric 20100204 London
Eric 20100209 Copenhagen

when the output is:

John 2010206
Eric 2010209

the sql could be something like

select name
, max(date)
from tablename
group by name

when the output is:

John 2010206 Amsterdam
Eric 2010209 Copenhagen

how should the the sql be?

select name
, max(date)
, location -- this is not working
from tablename
group by name

an aggregation error is given

when somebody has a good idea please help

Lambik

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-06 : 15:54:51

[code]select a.name,a.date,b.location
from
(
select name
, max(date) as date
from tablename
group by name
) a
inner join tablename b on a.name = b.name and a.date = b.date[/code]

Lambik
Starting Member

13 Posts

Posted - 2010-05-07 : 02:52:14

Thanx vijayisonly your query is working great

greet Lambik

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-05-07 : 03:09:06

CREATE TABLE #TMPS
(
name varchar(100),
dates datetime,
location varchar(100)
)

insert into #TMPS
select 'John','20100202','Paris'
union
select 'John','20100204','Berlin'
union
select 'John','20100206','Amsterdam'
union
select 'Eric','20100204','London'
union
select 'Eric','20100209','Copenhagen'

select t.*
from
(
select *,[rowno]=ROW_NUMBER() over (partition by name order by dates desc) from #TMPS
) t
where t.rowno =1
order by t.name

Iam a slow walker but i never walk back

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-05-07 : 03:31:54

quote:

Originally posted by vijayisonly

select a.name,a.date,b.location
from
(
select name
, max(date) as date
from tablename
group by name
) a
inner join tablename b on a.name = b.name and a.date = b.date

Hi
yours will work fine but what happens when the same person has more than 1 record with same date. then u will get 2 records for john then one.
thanks

Iam a slow walker but i never walk back

Lambik
Starting Member

13 Posts

Posted - 2010-05-07 : 04:34:29

Dineshajan

I have tried it with more than 1 record with same date :
the first solution will indeed return 2 rows. Your solution return one row.
In our company I will see both the visits. (2 visits on the same date time is not possible however)

your sql syntax is new for me Dineshajan (select *,[rowno]=ROW_NUMBER() over (partition by name order by dates desc)
I will read something about it thanx anyway

Lambik

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-05-07 : 05:01:21

Hi,

What u said is correct. U can try this. this will help urs.

select t.*
from
(
select *,[rowno]=dense_rank() over (partition by name order by dates desc) from #TMPS
) t
where t.rowno =1

Iam a slow walker but i never walk back

Lambik
Starting Member

13 Posts

Posted - 2010-05-07 : 05:04:44

Dineshrajan

yes that will return all the rows thx

Lambik

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-05-07 : 05:07:28

Welcome

Iam a slow walker but i never walk back

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources