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)
 How to Select Max row ?

Author  Topic 

thangnc
Starting Member

7 Posts

Posted - 2013-03-28 : 11:58:04
My table is:

id home datetime player resource
---|-----|------------|--------|---------
1 | 10 | 04/03/2009 | john | 399
2 | 11 | 04/03/2009 | juliet | 244
5 | 12 | 04/03/2009 | borat | 555
3 | 10 | 03/03/2009 | john | 300
4 | 11 | 03/03/2009 | juliet | 200
6 | 12 | 03/03/2009 | borat | 500
7 | 13 | 24/12/2008 | borat | 600
8 | 13 | 01/01/2009 | borat | 700

I need to select each distinct "home" holding the maximum value of "datetime".
Result would be:

id home datetime player resource
---|-----|------------|--------|---------
1 | 10 | 04/03/2009 | john | 399
2 | 11 | 04/03/2009 | juliet | 244
5 | 12 | 04/03/2009 | borat | 555
8 | 13 | 01/01/2009 | borat | 700

Please help me!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-28 : 12:11:15
[code]
SELECT id, home, [datetime], player, resource
FROM
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY home ORDER BY datetime DESC) AS Rn
)t
WHERE Rn=1
[/code]

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

Go to Top of Page

thangnc
Starting Member

7 Posts

Posted - 2013-03-29 : 09:36:07
Thank visakh16 help me!
But I can not get result below:

DECLARE @TestTable TABLE (id INT, home INT, date DATETIME,
player VARCHAR(20), resource INT)

INSERT INTO @TestTable
SELECT 1, 1, '2009-03-04', 'john', 399 UNION
SELECT 3, 1, '2009-03-03', 'john', 300 UNION
SELECT 10, 2, '2009-03-04', 'john', 399 UNION
SELECT 4, 1, '2009-03-03', 'juliet', 200 UNION
SELECT 2, 1, '2009-03-04', 'juliet', 244 UNION
SELECT 5, 1, '2009-03-04', 'borat', 555 UNION
SELECT 7, 1, '2008-12-24', 'borat', 600 UNION
SELECT 8, 1, '2009-01-01', 'borat', 700 UNION
SELECT 9, 2, '2009-01-01', 'borat', 700

Result I want to get:
id home datetime player resource
-- |----|------------|--------|---------
2 | 1 | 04/03/2009 | juliet | 244
5 | 1 | 04/03/2009 | borat | 555
10 | 2 | 04/03/2009 | john | 399
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-29 : 09:42:04
whats the logic behind that output?
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-03-29 : 11:21:15
I dont know how come resouce is getting 700 in the desired ouput you mentioned for John...may be its a typo on your side .. and may be this is what you're looking for

DECLARE @TestTable TABLE (id INT, home INT, date DATETIME,
player VARCHAR(20), resource INT)
INSERT INTO @TestTable
SELECT 1, 1, '2009-03-04', 'john', 399 UNION
SELECT 3, 1, '2009-03-03', 'john', 300 UNION
SELECT 10, 2, '2009-03-04', 'john', 399 UNION
SELECT 4, 1, '2009-03-03', 'juliet', 200 UNION
SELECT 2, 1, '2009-03-04', 'juliet', 244 UNION
SELECT 5, 1, '2009-03-04', 'borat', 555 UNION
SELECT 7, 1, '2008-12-24', 'borat', 600 UNION
SELECT 8, 1, '2009-01-01', 'borat', 700 UNION
SELECT 9, 2, '2009-01-01', 'borat', 700

SELECT Max(A.Id)ID,Max(A.home) home,A.date,A.player,Sum(A.resource) Resource
FROM @TestTable A Inner Join
(
SELECT Max(date) Date,player FROM @TestTable
Group by player
)B on A.player=B.player and A.date=B.Date
Group by A.date,A.player
order by 1

Cheers
MIK
Go to Top of Page

thangnc
Starting Member

7 Posts

Posted - 2013-03-29 : 11:47:47
Sorry All!
result is:
id home datetime player resource
-- |----|------------|--------|---------
2 | 1 | 04/03/2009 | juliet | 244
5 | 1 | 04/03/2009 | borat | 555
10 | 2 | 04/03/2009 | john | 399

Thank MIK, but my result not sum resource
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-03-29 : 11:49:06
Try this

DECLARE @TestTable TABLE (id INT, home INT, date DATETIME,
player VARCHAR(20), resource INT)
INSERT INTO @TestTable
SELECT 1, 1, '2009-03-04', 'john', 399 UNION
SELECT 3, 1, '2009-03-03', 'john', 300 UNION
SELECT 10, 2, '2009-03-04', 'john', 399 UNION
SELECT 4, 1, '2009-03-03', 'juliet', 200 UNION
SELECT 2, 1, '2009-03-04', 'juliet', 244 UNION
SELECT 5, 1, '2009-03-04', 'borat', 555 UNION
SELECT 7, 1, '2008-12-24', 'borat', 600 UNION
SELECT 8, 1, '2009-01-01', 'borat', 700 UNION
SELECT 9, 2, '2009-01-01', 'borat', 700

SELECT Max(A.Id)ID,Max(A.home) home,A.date,A.player,A.resource Resource
FROM @TestTable A Inner Join
(
SELECT Max(date) Date,player FROM @TestTable
Group by player
)B on A.player=B.player and A.date=B.Date
Group by A.date,A.player,A.resource
order by 1

Cheers
MIK
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-29 : 14:03:00
[code]
SELECT id, home, [datetime], player, resource
FROM
(
SELECT *,DENSE_RANK() OVER (ORDER BY datetime DESC) AS Rn,
ROW_NUMBER() OVER (PARTITION BY datetime,resource ORDER BY id DESC) AS Seq
FROM Table
)t
WHERE Rn=1
AND Seq=1
[/code]


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

Go to Top of Page

thangnc
Starting Member

7 Posts

Posted - 2013-04-01 : 05:09:44
Thank visakh16!
It worked very good!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-01 : 06:39:48
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -