CODEWITHSUNDEEP
mysql
Shantesh
Shantesh

Reputation: 1600

Have to get the corresponding time stamp when i get max of a column from a table

I need to extract the required fields from a table along with relevant time stamp

SELECT * FROM Glm_Test.LicenseUsage where FeatureId='2';
Output :
VendorId,FeatureId,Total_Lic_Installed,Total_Lic_Used,Reserved,CurrentTime
1   2   106 19  67  2015-12-15 15:00:05
1   2   106 19  67  2015-12-15 15:02:02
1   2   106 19  69  2015-12-15 15:04:02
1   2   106 19  67  2015-12-15 15:06:01
1   2   106 20  67  2015-12-15 15:08:02
select VendorId,FeatureId,Total_Lic_Installed,Max(Total_Lic_Used),Reserved,CurrentTime from Glm_Test.LicenseUsage where FeatureId= '2' group by VendorId,FeatureId;
output:
1   2   106 20  69  2015-12-15 15:00:05

In the above 2 queries 1st query lists all entries from the table

and i want second query to return time stamp for the MAX value of column Total_Lic_Used but somehow it is returning me only timestamp of the first entry.

Help is much appreciated.

Upvotes: 0

Views: 31

Answers (1)

Praveen
Praveen

Reputation: 9345

Selecting the columns which are not part of an aggregation function like count/max/min/sum... or not in group by clause will give unexpected results:

Other RBBMS wont allow these statements(gives error like):

sql server ==> the select list because it is not contained in either an aggregate function or the GROUP BY clause

Oracle ==>not a GROUP BY expression

You can do this by a sub query and join

select 
    a.VendorId,
    a.FeatureId,
    a.Total_Lic_Installed,
    b.max_Total_Lic_Used,
    a.Reserved,
    a.CurrentTime 
from Glm_Test.LicenseUsage a 
join (
    select 
        VendorId,
        FeatureId,
        Max(Total_Lic_Used) max_Total_Lic_Used
    from Glm_Test.LicenseUsage 
    where FeatureId = '2' 
    group by VendorId, FeatureId
) b
on a.VendorId = b.VendorId and 
a.FeatureId = b.FeatureId and
a.Total_Lic_Used = b.max_Total_Lic_Used

sql fiddle demo

You can try this also;

select 
    `VendorId`, 
    `FeatureId`, 
    `Total_Lic_Installed`, 
    `Total_Lic_Used`, 
    `Reserved`, 
    `CurrentTime`
from Glm_Test.LicenseUsage
order by Total_Lic_Used desc
limit 1

demo

Upvotes: 1

Related Questions