Results 1 to 5 of 5
  1. #1
    Join Date
    May 2012
    Posts
    3

    Unhappy Unanswered: Select last record by group

    Hello, everyone! Can you please help me with this query to show last record/Partner or PartnerName.

    select 'PURCHASE' as EntityName, d.PartnerName, h.*
    from (
    select MAX([TimeStamp]) [Data import], COUNT(1) [Numar de inregistrari], StartDate, EndDate, DistributorId
    from DataImport.PurchaseHistory
    group by DistributorId, StartDate, EndDate
    ) h
    inner join Partner d on d.PartnerId = h.DistributorId
    where d.Active = 1
    order by DistributorId, StartDate desc, EndDate desc

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Hi,

    Can you post a sample of data from tables and its expected result?

  3. #3
    Join Date
    May 2012
    Posts
    3
    Quote Originally Posted by imex View Post
    Hi,

    Can you post a sample of data from tables and its expected result?
    This is the table.
    Only results in red rectangle must be showed.
    This results can be filtered by 'PartnerName' or 'DistributorID', and must be last by 'Data Import'

    Thank you for interest!
    Attached Thumbnails Attached Thumbnails Untitled.jpg  

  4. #4
    Join Date
    Apr 2012
    Posts
    213
    Try something like that:

    Code:
    with CTE as
    (
        select 
            [TimeStamp] as [Data import], 
            COUNT(1) OVER(PARTITION BY DistributorId) [Numar de inregistrari], 
            ROW_NUMBER() OVER(PARTITION BY DistributorId ORDER BY [TimeStamp] DESC) AS RowNo,
            StartDate, 
            EndDate, 
            DistributorId
        from DataImport.PurchaseHistory 
        -- where DistributorId = @DistributorId
    )
    
    select 'PURCHASE' as EntityName, d.PartnerName, h.*
    from CTE as h
    inner join Partner d on d.PartnerId = h.DistributorId
    where (h.RowNo = 1) and (d.Active = 1) -- and (d.PartnerName = @PartnerName)
    order by DistributorId, StartDate desc, EndDate desc
    Hope this helps.

  5. #5
    Join Date
    May 2012
    Posts
    3

    Smile

    Quote Originally Posted by imex View Post
    Try something like that:

    Code:
    with CTE as
    (
        select 
            [TimeStamp] as [Data import], 
            COUNT(1) OVER(PARTITION BY DistributorId) [Numar de inregistrari], 
            ROW_NUMBER() OVER(PARTITION BY DistributorId ORDER BY [TimeStamp] DESC) AS RowNo,
            StartDate, 
            EndDate, 
            DistributorId
        from DataImport.PurchaseHistory 
        -- where DistributorId = @DistributorId
    )
    
    select 'PURCHASE' as EntityName, d.PartnerName, h.*
    from CTE as h
    inner join Partner d on d.PartnerId = h.DistributorId
    where (h.RowNo = 1) and (d.Active = 1) -- and (d.PartnerName = @PartnerName)
    order by DistributorId, StartDate desc, EndDate desc
    Hope this helps.
    It's work and it's great!
    Thank you!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •