Question about query - 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 2008 Forums

Transact-SQL (2008)

Question about query

Author

Topic

flion
Starting Member

8 Posts

Posted - 2010-04-27 : 06:46:38

Hi,

I've got a problem with my sql query, I need a result like this:

Pete, 27-04-2010, 15658, N
Jeffrey, 27-04-2010, 57954, N
Mike, 26-04-2010, 79851, N
Peter, 25-04-2010, 684587, Y

My database looks like this:

Name, date, points, alone
Pete, 25-04-2010, 1896, Y
Jeffrey, 25-04-2010, 368, Y
Mike, 24-04-2010, 245, N
Peter, 23-04-2010, 657, Y
Pete, 26-04-2010, 1248, N
Jeffrey, 26-04-2010, 218, Y
Mike, 25-04-2010, 878, Y
Peter, 24-04-2010, 65, Y
Pete, 27-04-2010, 15658, N
Jeffrey, 27-04-2010, 57954, N
Mike, 26-04-2010, 79851, N
Peter, 25-04-2010, 684587, Y

So I need to retrieve all the persons once, with the most recent info (date, points, alone).

I hope I made myself clear?

Many thanks in advance!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-27 : 06:50:11

select
Name,
max(date) as Date
from table
group by Name

No, you're never too old to Yak'n'Roll if you're too young to die.

flion
Starting Member

8 Posts

Posted - 2010-04-27 : 08:07:10

quote:
Originally posted by webfred

select
Name,
max(date) as Date
from table
group by Name

No, you're never too old to Yak'n'Roll if you're too young to die.

Then I receive an error that it is not contained in either an aggregate function or the GROUP BY clause.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-27 : 08:15:48

Huh?
Can you show your statement that errors?

No, you're never too old to Yak'n'Roll if you're too young to die.

flion
Starting Member

8 Posts

Posted - 2010-04-27 : 13:12:03

quote:
Originally posted by webfred

Huh?
Can you show your statement that errors?

No, you're never too old to Yak'n'Roll if you're too young to die.

Looks that I just made a mistake. I will check it tomorrow.

Thanks for your help mate!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-27 : 13:46:51

quote:
Originally posted by flion

quote:
Originally posted by webfred

Huh?
Can you show your statement that errors?

No, you're never too old to Yak'n'Roll if you're too young to die.

Looks that I just made a mistake. I will check it tomorrow.

Thanks for your help mate!

I think you've used someother additional fields in select. once you group by a field you cannot directly use other fields in select. you need to apply aggregate functions on them.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

flion
Starting Member

8 Posts

Posted - 2010-04-27 : 14:28:18

Hmm, just tried this:

select Name, max(date) as Date, points, alone
from table
group by Name, points, alone

But now I get too much records. When I use just Name and date it works flawless. But I need more info then just those 2.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2010-04-27 : 14:32:50

select
name, date, points, alone from (
select name, date, points, alone, row_number() over(partition by name order by date desc) as rnk from table)t
where rnk = 1

flion
Starting Member

8 Posts

Posted - 2010-04-27 : 15:10:35

That looks exactly what I need. Many thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-28 : 06:04:18

or you earlier query with this addition


select t.Name,t.Date, t.points, t.alone
from table t
join(
select Name, max(date) as Date
from table
group by Name
)t1
on t1.date=t.date
and t1.Name=t.Name

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources