CODEWITHSUNDEEP
sqlsql-servert-sql
Kasper Hansen
Kasper Hansen

Reputation: 6547

SQL and use of nested queries

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:

  1. Retrieve a list of all the texts known as ChargeId.
  2. There are many of each, so each should have the latest date on.
  3. 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.

enter image description here

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.

enter image description here

Upvotes: 0

Views: 396

Answers (3)

Madhivanan
Madhivanan

Reputation: 13700

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

Upvotes: 0

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14077

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.

Upvotes: 0

Felix Pamittan
Felix Pamittan

Reputation: 31879

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.

Upvotes: 1

Related Questions