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 | 2445 | 12 | 04/03/2009 | borat | 5553 | 10 | 03/03/2009 | john | 3004 | 11 | 03/03/2009 | juliet | 2006 | 12 | 03/03/2009 | borat | 5007 | 13 | 24/12/2008 | borat | 6008 | 13 | 01/01/2009 | borat | 700I 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 | 3992 | 11 | 04/03/2009 | juliet | 2445 | 12 | 04/03/2009 | borat | 5558 | 13 | 01/01/2009 | borat | 700Please 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)tWHERE Rn=1[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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 @TestTableSELECT 1, 1, '2009-03-04', 'john', 399 UNIONSELECT 3, 1, '2009-03-03', 'john', 300 UNIONSELECT 10, 2, '2009-03-04', 'john', 399 UNIONSELECT 4, 1, '2009-03-03', 'juliet', 200 UNIONSELECT 2, 1, '2009-03-04', 'juliet', 244 UNIONSELECT 5, 1, '2009-03-04', 'borat', 555 UNIONSELECT 7, 1, '2008-12-24', 'borat', 600 UNIONSELECT 8, 1, '2009-01-01', 'borat', 700 UNIONSELECT 9, 2, '2009-01-01', 'borat', 700Result I want to get:id home datetime player resource-- |----|------------|--------|---------2 | 1 | 04/03/2009 | juliet | 2445 | 1 | 04/03/2009 | borat | 55510 | 2 | 04/03/2009 | john | 399 |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-29 : 09:42:04
|
whats the logic behind that output? |
|
|
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 forDECLARE @TestTable TABLE (id INT, home INT, date DATETIME, player VARCHAR(20), resource INT)INSERT INTO @TestTableSELECT 1, 1, '2009-03-04', 'john', 399 UNIONSELECT 3, 1, '2009-03-03', 'john', 300 UNIONSELECT 10, 2, '2009-03-04', 'john', 399 UNIONSELECT 4, 1, '2009-03-03', 'juliet', 200 UNIONSELECT 2, 1, '2009-03-04', 'juliet', 244 UNIONSELECT 5, 1, '2009-03-04', 'borat', 555 UNIONSELECT 7, 1, '2008-12-24', 'borat', 600 UNIONSELECT 8, 1, '2009-01-01', 'borat', 700 UNIONSELECT 9, 2, '2009-01-01', 'borat', 700SELECT Max(A.Id)ID,Max(A.home) home,A.date,A.player,Sum(A.resource) ResourceFROM @TestTable A Inner Join ( SELECT Max(date) Date,player FROM @TestTable Group by player)B on A.player=B.player and A.date=B.DateGroup by A.date,A.playerorder by 1CheersMIK |
|
|
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 | 2445 | 1 | 04/03/2009 | borat | 55510 | 2 | 04/03/2009 | john | 399Thank MIK, but my result not sum resource |
|
|
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 @TestTableSELECT 1, 1, '2009-03-04', 'john', 399 UNIONSELECT 3, 1, '2009-03-03', 'john', 300 UNIONSELECT 10, 2, '2009-03-04', 'john', 399 UNIONSELECT 4, 1, '2009-03-03', 'juliet', 200 UNIONSELECT 2, 1, '2009-03-04', 'juliet', 244 UNIONSELECT 5, 1, '2009-03-04', 'borat', 555 UNIONSELECT 7, 1, '2008-12-24', 'borat', 600 UNIONSELECT 8, 1, '2009-01-01', 'borat', 700 UNIONSELECT 9, 2, '2009-01-01', 'borat', 700SELECT Max(A.Id)ID,Max(A.home) home,A.date,A.player,A.resource ResourceFROM @TestTable A Inner Join ( SELECT Max(date) Date,player FROM @TestTable Group by player)B on A.player=B.player and A.date=B.DateGroup by A.date,A.player,A.resourceorder by 1CheersMIK |
|
|
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 SeqFROM Table)tWHERE Rn=1AND Seq=1[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
thangnc
Starting Member
7 Posts |
Posted - 2013-04-01 : 05:09:44
|
Thank visakh16!It worked very good! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-01 : 06:39:48
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|