SQL View Question

  • Jonathan AC Roberts

    SSCoach

    Points: 17319

    sdotnet wrote:

    the TOP 1 doesn't works and this is the reason why I'm here. If there's a query to do this, and you want help me, good.....the rest is my problem. How to adjust the name, the position, the data and other.

    Thanks

    TOP(1) does work but you need to use it inside a CROSS APPLY.

    This has been said to you multiple times. What don't you understand about it?

  • Steve Collins

    SSC Eights!

    Points: 883

    ;with
    details_summary_cte(Manage_GUID, details_row_count) as (
    select Manage_GUID,
    count(*) /* ... other aggregate functions */
    from dbo.Details
    group by Manage_GUID),
    payments_summary_cte(Manage_GUID, payments_row_count) as (
    select Manage_GUID,
    count(*) /* ... other aggregate functions */
    from dbo.Payments
    group by Manage_GUID)
    select
    m.ID, m.[Type], m.Customer, m.[Date],
    dsc.details_row_count,
    psc.payments_row_count
    from
    dbo.Manage m
    join
    details_summary_cte dsc ON m.[GUID] = dsc.Manage_GUID
    join
    payments_summary_cte psc on m.[GUID] = psc.Manage_GUID;
  • Jeffrey Williams

    SSC Guru

    Points: 88593

    This is an absolute guess - since you have not provided any sample data to work with...

     Select Top 1 With Ties 
    m.ID
    , m.Type
    , m.Customer
    , m.Date
    , d.Order
    , d.Order_Date
    , p.Amount
    , p.PayDate
    , p.PayState
    From dbo.Manage m
    Inner Join dbo.Details d On d.GUID = d.Manage_GUID
    Inner Join dbo.Payments p On p.Manage_GUID = d.GUID
    Order By
    row_number() over(Partition By d.GUID Order By p.PayDate desc);

    I am assuming you would want the latest payment date per GUID - but if not you can adjust the partition in the row_number (or even rank/dense_rank if needed).

    With that said - if you want accurate help for a problem you need to define the problem in a way that is understandable.  The best way to do that in a forum is to provide sample data (create a temp table - provide insert statements for sample data) and a query that utilizes the sample data up to where you are having an issue.  By providing this...you allow those of us who volunteer our time (in many cases - very valuable time) to help you resolve your issue.

    For future reference - please refer to the link in my signature to review how to post questions to get better answers.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720426

    Let me step back, why doesn't top 1 work? This will get you a row from Manage and a matching from both Details and Payments. If you determine that, you might be able to solve this, or ask a better question.

    A number of people have asked you for some DDL or linked to a explanation. We are looking for something like this.

    DROP TABLE Manage
    DROP TABLE Detail
    DROP TABLE Payment
    go
    CREATE TABLE Manage
    ( ManageID UNIQUEIDENTIFIER
    , ManageName VARCHAR(10));
    GO

    CREATE TABLE Detail
    ( DetailID INT
    , ManageID UNIQUEIDENTIFIER
    , DetailName VARCHAR(10));
    GO

    CREATE TABLE Payment
    ( PaymentID INT
    , ManageID UNIQUEIDENTIFIER
    , PaymentDate DATE
    , PaymentAmount NUMERIC(10, 4));
    GO

    DECLARE @n UNIQUEIDENTIFIER = NEWID();
    INSERT Manage SELECT @n, 'Steve';
    INSERT Detail VALUES (1, @n, 'First'), (2, @n, 'Second');
    INSERT Payment VALUES (1, @n, '2020-01-01', 100);
    SELECT @n = NEWID();
    INSERT Manage SELECT @n, 'Andy';
    INSERT Detail VALUES (3, @n, 'First'), (4, @n, 'Third');
    INSERT Payment VALUES (2, @n, '2020-01-01', 100), (3, @n
    , '2020-02-01', 200);

    Tables and data. Then, include your view and explain what is wrong in the result.

    Your writing is not explaining the issue.

Viewing 4 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply