SQL and use of nested queries

Question

I have difficulties forming the right query for this problem. It's probably not very hard though. I was given a hint and told to redo it using nested queries though, but need some help.

Basically I need to do this:

Retrieve a list of all the texts known as ChargeId.
There are many of each, so each should have the latest date on.
If a ChargeId are there more than once with the latest date, then they are versioned. So take the one with the latest version in that case.

Basically these are electricity prices and they are changed in a Windows form window. The date is when they are valid from. And if the user changes some of them without changing the date, then it just gets a newer version. Version 3 becomes version 4. Here is my attempt which obviously returns too much because each ChargeId appears more than one time.

Note here that all the relevant prices has version 4. They do not have to have the same version or the same date. It is just simply "retrieve all prices with the latest date for each. If more than one ChargeId line is there, then take the one with highest version."

Edit: I thought this would work, but not all ChargeId's are there.

Felix Pamittan · Accepted Answer · 2015-08-20 06:55:07Z

1

I believe you can do this using ROW_NUMBER:

Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

SELECT ChargeId, Version, StartDate
FROM (
    SELECT *,
        RN = ROW_NUMBER() OVER(PARTITION BY ChargeId ORDER BY StartDate DESC, Version DESC)
    FROM SWMarket.Price
    WHERE CompanyId = 1
)AS p
WHERE RN = 1

Latest date is obtained using ORDER BY StartDate DESC. If there are multiple rows for the latest StartDate, then we get the latest Version using ORDER BY Version DESC.

answered Aug 20, 2015 at 6:55

Felix Pamittan

31.9k7 gold badges43 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Evaldas Buinauskas · Accepted Answer · 2015-08-20 06:55:15Z

0

It looks like you never used RANKING functions in SQL Server. This is query that should work for you:

SELECT *
FROM (
    SELECT P.ChargeID
        , P.[Version]
        , P.StartDate
        , ROW_NUMBER() OVER (PARTITION BY P.ChargeId ORDER BY P.[Version] DESC) AS RN
    FROM SWMarket.Price AS P
    WHERE P.CompanyID = 1
    ) AS T
WHERE T.RN = 1;

Query partitions your table results by ChargeID and assign unique ROW_NUMBER() for each ChargeID based on its version (in a descending order). So the highest version will have RN = 1, second highest version will have RN = 2 and so on. So at this point it's quite straightforward to pick all records with RN = 1, they will bring ChargeIDs with their latest versions.

answered Aug 20, 2015 at 6:55

Evaldas Buinauskas

14.2k11 gold badges63 silver badges119 bronze badges

Comments

Madhivanan · Accepted Answer · 2015-08-20 06:55:21Z

0

You may need to find max versioned. So the code should be

select chargeid,max(versioned) as versioned, date from
(

your above code

) as t
group by chargeid, date

answered Aug 20, 2015 at 6:55

Madhivanan

13.7k1 gold badge28 silver badges30 bronze badges

Collectives™ on Stack Overflow

SQL and use of nested queries

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related