SQL Server 2008 How can I SELECT rows with Value by GROUP(ing) other column?

Question

Hi guys I'm a beginner at SQL so please bear with me.. :)

My question is as follows.

I got this table:

     DateTime         ID     Year    Month   Value    Cost
-------------------|------|--------|-------|-------|--------|
 1-1-2013 00:00:01 |  1   |  2013  |   1   |  30   |   90   |
 1-1-2013 00:01:01 |  1   |  2013  |   1   |  0    |   0    |
 1-1-2013 00:02:01 |  1   |  2013  |   1   |  1    |   3    |
 1-2-2013 00:00:01 |  1   |  2013  |   2   |  2    |   6    |
 1-2-2013 00:01:01 |  1   |  2013  |   2   |  3    |   9    |
 1-2-2013 00:02:01 |  1   |  2013  |   2   |  4    |   12   |
 1-3-2013 00:00:01 |  1   |  2013  |   3   |  5    |   15   |
 1-3-2013 00:01:01 |  1   |  2013  |   3   |  6    |   18   |
 1-3-2013 00:02:01 |  1   |  2013  |   3   |  7    |   21   |

Now what I'm trying to get is this result

   Year    Month   Value    Cost
|--------|-------|-------|--------|
|  2013  |   1   |  1    |   3    |
|  2013  |   2   |  4    |   12   |
|  2013  |   3   |  7    |   21   |

As you can see I'm trying to GROUP BY the [Month] and the [Year] and to get the last [Value] for every [Month].

Now as you can understand from the result I do not try to get the MAX() value from the [Value] column but the last value for every [Month] and that is my issue..

Thanks in advance

PS
I was able to GROUP BY the [Year] and the [Month] but as I understand that when I adding the [Value] column the GROUP BY is not effecting the result, as the SQL need more spcification on the value you what the SQL to get..

score 1 · Answer 1 · edited May 23 '17 at 10:24

1

Instead of using row_number(), you can also use rank(). Using rank() might give you multiple values within the same year and month, see this post. Because of this, a group by is added.

SELECT
  [Year],
  [Month],
  [Value],
  [Cost]
FROM
(
  SELECT 
    [Year],
    [Month],
    [Value],
    [Cost],
    Rank() OVER (PARTITION BY [Year], [Month] ORDER BY [DateTime] DESC) AS [Rank]
  FROM [t1]
) AS [sub]
WHERE [Rank] = 1
GROUP BY 
  [Year],
  [Month],
  [Value],
  [Cost]
ORDER BY
  [Year] ASC,
  [Month] ASC

As stated in the comments, this might still return multiple records for a single month. Therefor the ORDER BY statement can be extended, based on the desired functionality:

Rank() OVER (PARTITION BY [Year], [Month] ORDER BY [DateTime] DESC, [Value] DESC, [Cost] ASC) AS [Rank]

Switching the order of [Value] and [Cost] or ASC <> DESC will influence the rank and because of that the result.

edited May 23 '17 at 10:24

Community

1
1

answered Jan 08 '13 at 11:38

Jacco

3,251
1
19
29

I was trying that script and the result logic is as I needed but now I can see that when I've more then one max time stampes at [DateTime] I'm getting all of them, when I need just one. What can I do to fix that? – BujinRikimaru Jan 08 '13 at 12:14
I see what you mean. You'll have to extend the ORDER BY used for ranking. Depending on what suits you best. I'll expand my answer in a moment. – Jacco Jan 08 '13 at 12:22
THX m8, but I got the "V" mark to @bluefeet as his first example was giving me the exact logic I searched for and the performance are suited for me. Cheers. – BujinRikimaru Jan 08 '13 at 12:29
If not already, do have a look at the post mentioned in my answer. It is wise to extend the ORDER BY, so you know exactly how the rownumber or rank is determined. Only ordering by [DateTime] leaves a small factor of randomness. – Jacco Jan 08 '13 at 12:35
Yes I did saw it, and I've tried u'r fix too, It works. I agree with u about how to determine "what goes where" in the rownumber or rank window, so I'll refactor my script as of that. THX again. (If it was possible I was giving u the "V" mark too ;)) – BujinRikimaru Jan 08 '13 at 13:22

Taryn · Accepted Answer · 2013-01-08T11:29:07.563

Since you are using SQL Server 2008, you can use row_number() to get the result:

select year, month, value, cost
from
(
  select year, month, value, cost,
    row_number() over(partition by year, month order by datetime desc) rn
  from yourtable
) src
where rn = 1

See SQL Fiddle with Demo

Or you can use a subquery to get this (note: with this version if you have more than one record with the same max datetime per month then you will return each record:

select t1.year, t1.month, t1.value, t1.cost
from yourtable t1
inner join
(
  select max(datetime) datetime
  from yourtable
  group by year, month
) t2
  on t1.datetime = t2.datetime

See SQL Fiddle with Demo

Both give the same result:

| YEAR | MONTH | VALUE | COST |
-------------------------------
| 2013 |     1 |     1 |    3 |
| 2013 |     2 |     4 |   12 |
| 2013 |     3 |     7 |   21 |

Thanks! That was helpful, I used u'r first sample and I got exact what I needed. cheers m8. — BujinRikimaru, Jan 08 '13 at 12:24

SQL Server 2008 How can I SELECT rows with Value by GROUP(ing) other column?

2 Answers2