4 Replies - 212 Views - Last Post: 24 September 2018 - 09:59 AM

#1 RchLuvSlly   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 21
  • Joined: 26-June 12

SQL Server 2008 R2 Choose Certain Rows on Certain Conditions

Posted 24 September 2018 - 09:08 AM

Hi all, I have a problem. See table below:

Item | In_Date | Qty_Balance | Created_At

A    | 1       | 90          | 2018-09-26 16:09:26.523

A    | 1       | 96          | 2018-09-26 15:26:12.507

A    | 2       | 50          | 2018-09-26 15:24:43.617

A    | 2       | 45          | 2018-09-24 15:47:40.977


As you can see, the table is ordered by column "Created_At" desc. Now, what I need is to get the "Qty_Balance" data but only the last entered one for each item. So, for A, I need to get the "Qty_Balance" of 90 whereas for the second A, I need to get the "Qty_Balance" of 50. We can tell which data is last entered by referring to the "Created_At" column, which is why the column is ordered descending. So, just to be clear, what I need is:

Item | In_Date | Qty_Balance | Created_At

A    | 1       | 90          | 2018-09-26 16:09:26.523

A    | 2       | 50          | 2018-09-26 15:24:43.617
:code:/>

Although the items are both A, they are considered different when they have different in_date.
How can I possibly achieve this in Microsoft SQL Server 2008 R2? Please kindly help me. Thank you in advance.

This post has been edited by RchLuvSlly: 24 September 2018 - 09:10 AM
Reason for edit:: In the future, please use the [code] tag button in the editor.


Is This A Good Question/Topic? 0
  • +

Replies To: SQL Server 2008 R2 Choose Certain Rows on Certain Conditions

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15105
  • View blog
  • Posts: 60,391
  • Joined: 12-June 08

Re: SQL Server 2008 R2 Choose Certain Rows on Certain Conditions

Posted 24 September 2018 - 09:12 AM

Perhaps use a combination of group by and 'max'.
https://docs.microso...sql-server-2017
Was This Post Helpful? 0
  • +
  • -

#3 RchLuvSlly   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 21
  • Joined: 26-June 12

Re: SQL Server 2008 R2 Choose Certain Rows on Certain Conditions

Posted 24 September 2018 - 09:30 AM

View Postmodi123_1, on 24 September 2018 - 09:12 AM, said:

Perhaps use a combination of group by and 'max'.
https://docs.microso...sql-server-2017

Hi, thank you for the quick response. Initially, I was about to kindly request that you elaborate. However, I have acquired the solution to my problem from another forum. Anyway, thank you again for your response. :)
Was This Post Helpful? 0
  • +
  • -

#4 RchLuvSlly   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 21
  • Joined: 26-June 12

Re: SQL Server 2008 R2 Choose Certain Rows on Certain Conditions

Posted 24 September 2018 - 09:36 AM

Just in case this might help anyone in the future, here's the solution:
Select 
    Item , 
    In_Date ,
    Qty_Balance , 
    Created_At
from (
    SELECT *,ROW_NUMBER() OVER(PARTITION BY In_Date ORDER BY Created_At DESC) rn
    FROM T
)t1 
where rn = 1


What this does is that it partitions the data by the in_date column, which is the key determining column in my case, and for the same in_date, SQL will provide continuous row number (1, 2, 3, etc.) and when the in_date changes, SQL will restart the row numbering from 1 again. Then, we take out each record whose row number is 1 because we already order the recordset by the created_at column in a descending order. So, it gives the correct results.
Was This Post Helpful? 1
  • +
  • -

#5 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7442
  • View blog
  • Posts: 15,437
  • Joined: 16-October 07

Re: SQL Server 2008 R2 Choose Certain Rows on Certain Conditions

Posted 24 September 2018 - 09:59 AM

First, thanks for returning with your solution. :)

Now, since I don't like that one...

Don't get me wrong, it's a reasonable MS SQL approach, but I prefer standard SQL where possible.

Here's some MS SQL specific setup, if anyone want's to play along at home:
create table #tt(Item char(1), In_Date int, Qty_Balance int, Created_At datetime)

insert into #tt values('A',1,90,'2018-09-26 16:09:26.523')
insert into #tt values('A',1,96,'2018-09-26 15:26:12.507')
insert into #tt values('A',2,50,'2018-09-26 15:24:43.617')
insert into #tt values('A',2,45,'2018-09-24 15:47:40.977')



Now, we want the last date for our rather wonky key:
SELECT Item,In_Date,max(Created_At)
  FROM #tt
  group by Item, In_Date



With this worried out, the solution is just an inner join away:
SELECT a.*
  from #tt a
    join (
      SELECT Item,In_Date,max(Created_At) as Created_At
        FROM #tt
        group by Item, In_Date
      ) b
      ON a.Item=b.Item and a.In_Date=b.In_Date and a.Created_At=b.Created_At



The above approach will working in any SQL compliant database and, additionally and importantly, will leverage indexes where available.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1