SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How to Select Max row ?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

thangnc
Starting Member

7 Posts

Posted - 03/28/2013 :  11:58:04  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 03/28/2013 :  12:11:15  Show Profile  Reply with Quote

SELECT id, home, [datetime], player, resource 
FROM
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY home ORDER BY datetime DESC) AS Rn
)t
WHERE Rn=1


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

Go to Top of Page

thangnc
Starting Member

7 Posts

Posted - 03/29/2013 :  09:36:07  Show Profile  Reply with Quote
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

Edited by - thangnc on 03/29/2013 11:44:00
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 03/29/2013 :  09:42:04  Show Profile  Reply with Quote
whats the logic behind that output?
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 03/29/2013 :  11:21:15  Show Profile  Reply with Quote
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

Edited by - MIK_2008 on 03/29/2013 11:21:54
Go to Top of Page

thangnc
Starting Member

7 Posts

Posted - 03/29/2013 :  11:47:47  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 03/29/2013 :  11:49:06  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 03/29/2013 :  14:03:00  Show Profile  Reply with Quote

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



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

Go to Top of Page

thangnc
Starting Member

7 Posts

Posted - 04/01/2013 :  05:09:44  Show Profile  Reply with Quote
Thank visakh16!
It worked very good!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/01/2013 :  06:39:48  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000