Get Primary key with SQL Group By Query

Get Primary key with SQL Group By Query

  

Hi,

I have below Entity i need all checklist with max version

IdCheckListIdVersionId
110
211
312
413
520
621
722
823



Expected Output will be below

IdCheckListIdVersionId
413
823


Same i am not able to do that in Aggregate and in SQL. Any Idea ?

Thanks & Regards

DM Singh

Hey DM,


Wouldn't something like this work?


SELECT A.Id, A.CheckListId, A.VersionId 
FROM Entity AS A 
WHERE A.VersionId = ( 
    SELECT max(B.VersionId)
    FROM Entity AS B 
    WHERE A.CheckListId = B.CheckListId 
)

Hi DM,

One option is to use the SQL option to write your own query, you can write a query with a subquery which gets the max id of version. Something like 

Select ID, CheckListId, VersionId from <Table> where VersionId= (select max(VersionId) from <table>)

Let me know if this works.

Shashank..

Hello DM,

You can do this in both Aggregate and SQL.

In aggregate, you need to Group By Id and CheckListId, than create a calculated field on VersionId using Max.

Cheers,
Eduardo Jauch

EDIT

Forget. I didn't "saw" the Id as an Id. :P 

Claring wrote:

Hey DM,


Wouldn't something like this work?


SELECT A.Id, A.CheckListId, A.VersionId 
FROM Entity AS A 
WHERE A.VersionId = ( 
    SELECT max(B.VersionId)
    FROM Entity AS B 
    WHERE A.CheckListId = B.CheckListId 
)

Yes it will work but it will take more time .. is there any other way to do with group by clause ?


Using a group... I don't think so.

Since you would group on CheckListId.

But then you would still need to filter out the highest version only.
You could do an orderBy on VersionId and Id (assuming the group by doesn't just grab a random Id instead).

But then you would still need to use a Foreach to loop through each CheckListId (Only leave the first/highest version of each CheckListId in the List or fill a new List with them)


It would be best if you already had a CheckListId since then could just do something like

SELECT Id, CheckListId, VersionId 
FROM Entity 
WHERE CheckListId = :CheckListId
AND VersionId = max(VersionId)

However technically you could achieve this with an INNER JOIN from CheckList to Entity.
But that would mean adding an extra join...


SELECT Entity.Id, Entity.CheckListId, Entity.VersionId 
FROM Entity
INNER JOIN CheckList on CheckList.Id = Entity.CheckListId 
WHERE Entity.VersionId = max(Entity.VersionId)


*Though I'm not sure if max(...) would work properly this way...

Hello DM,

Here you have attached a solution using aggregate.
But it will work ONLY if there are UNIQUE versions for the SAME CheckListId.

You can use the same approach in SQL, using only a single JOIN with the entity itself.

Cheers,
Eduardo Jauch

Hum... It seems the upload failed.
Here are some prints.

First you do a Left Join of the entity with itself.
Than, in the JOIN, you say you want to join where the class is the same, and the Version is lower in entity 1 than entity 2

Than, you just filter to show only the records that have entity 2 ID = NULL

And you have your results.
Hope this helps.

Cheers,
Eduardo Jauch

That's the only solution i got too. Just need to add the entity twice and on the first one use Group by on both the column, and on second one just use an aggregate of MAX and pass it to the Group Filter.


Shashank...