Sql get other fields from record that returned MAX value

Question

0.00/5 (No votes)

See more:

SQL is not my first language!

I want to locate a row using the MAX function but return other columns from that row.
Not sure if this is even possible?

Lets say we have a simple table:

Account, Invoice, DateTime, Value

I want to get the latest DateTime for each Account and the value associated with that row. Lets pretend there are no indexes and the Invoice is not guaranteed to be in any particular order.

So I can say:

SELECT Account, MAX(DateTime) AS Maxdate FROM Table GROUP BY Account

So far so good, but I also want to return the value column from the row that returned the latest DateTime but how can I do that?
I know I can inner join back to the same table, but the table is huge and that is very slow.

This is not exactly the data I am using but an illustration of what I want to return with the query.

It is not my database so I am not at liberty to add any indexes...!

Many thanks in anticipation.

What I have tried:

I have tried to select the records with the MAX function in a subquery but this is very slow.
I also tried inner joining the table to itself but again this is very slow.

Posted 29-Jun-17 2:40am

dnibbo

Updated 30-Jun-17 4:48am

v2

Add a Solution

4 solutions

Solution 1

You can try this out online at: SQL Fiddle[^]

SQL

CREATE TABLE ForgeRock
    ([productName] varchar(13), 
     [description] varchar(57),
     [price] int)
;
    
INSERT INTO ForgeRock
    ([productName], [description], [price])
VALUES
    ('OpenIDM', 'Platform for building enterprise provisioning solutions', 1),
    ('OpenAM', 'Full-featured access management', 2),
    ('OpenDJ', 'Robust LDAP server for Java', 3)
;


SELECT a.productName, a.description, a.price FROM ForgeRock a
	JOIN (SELECT MAX(t.price) AS max_subkey
	FROM ForgeRock t) b 
    ON b.max_subkey = a.price

Posted 29-Jun-17 3:29am

RickZeeland

Comments

dnibbo 30-Jun-17 10:17am

Hi, thanks for your reply but not allowed to create tables.
I know I can droop it straight after but I think my credentials will only have read permissions when this puppy goes live.
Really appreciate your time though.

Solution 2

--Temporary tables to the rescue my friend

create table maxdate(Account varchar(255),thedate datetime,thevalue int)

insert into maxdate(Account,thedate,thevalue) values('joe bloggs','2017-06-25',4)
insert into maxdate(Account,thedate,thevalue) values('joe bloggs','2017-06-26',2)
insert into maxdate(Account,thedate,thevalue) values('joe bloggs','2017-06-27',1)
insert into maxdate(Account,thedate,thevalue) values('joe bloggs','2017-06-28',8)
insert into maxdate(Account,thedate,thevalue) values('joe bloggs','2017-06-29',16)

insert into maxdate(Account,thedate,thevalue) values('Superman','2017-05-25',16)
insert into maxdate(Account,thedate,thevalue) values('Superman','2017-05-26',8)
insert into maxdate(Account,thedate,thevalue) values('Superman','2017-05-27',4)
insert into maxdate(Account,thedate,thevalue) values('Superman','2017-05-28',2)
insert into maxdate(Account,thedate,thevalue) values('Superman','2017-05-29',1)

insert into maxdate(Account,thedate,thevalue) values('Batman','2017-05-25',1)
insert into maxdate(Account,thedate,thevalue) values('Batman','2017-05-26',2)
insert into maxdate(Account,thedate,thevalue) values('Batman','2017-05-27',3)
insert into maxdate(Account,thedate,thevalue) values('Batman','2017-05-28',4)
insert into maxdate(Account,thedate,thevalue) values('Batman','2017-05-29',5)

if OBJECT_ID('tempdb..#accountlatestdate') is not null
begin
	drop table #accountlatestdate 
end 

SELECT Account,MAX(thedate) AS Maxdate
into #accountlatestdate
FROM maxdate
group by Account

select b.* 
from #accountlatestdate a 
join maxdate b on a.Account = b.Account and a.Maxdate = b.thedate

Results

joe bloggs	2017-06-29 00:00:00.000	16
Superman	2017-05-29 00:00:00.000	1
Batman	2017-05-29 00:00:00.000	5

Posted 29-Jun-17 5:40am

JJ-DBA

Comments

dnibbo 30-Jun-17 10:17am

Thanks for your time and your reply. However, as per proposal 1, think I will only have read permission Thanks anyway.

Solution 3

Assuming you're using Microsoft SQL Server 2008 or later, the ROW_NUMBER[^] function will probably help:

SQL

WITH cteOrderedData As
(
    SELECT
        Account,
        DateTime,
        Value,
        ROW_NUMBER() OVER (PARTITION BY Account ORDER BY DateTime DESC) As RN
    FROM
        Table
)
SELECT 
    Account, 
    DateTime AS Maxdate,
    Value
FROM 
    cteOrderedData
WHERE
    RN = 1
;

Posted 29-Jun-17 11:21am

Richard Deeming

Comments

dnibbo 30-Jun-17 10:35am

Hi, thanks for the suggestion. I tried your method and it does work but still the results are slow. Poor DB design to blame here...
I will add what I have found to be the quickest solution.
Thanks you for your time.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

dnibbo · Accepted Answer · 2017-06-30T04:48:00

Thanks to all for your time and suggestions.

What I have settled on is a two query solution which seems to be the quickest although still a little slower than I would like.

Perhaps I should have mentioned, the number of 'Accounts' is relatively low but the table has over 30million rows....

I now use

SELECT Phase1.Account, MaxDate, Value FROM
( SELECT Account, MAX(DateTime) FROM Table 
  GROUP BY Account ) Phase1
INNER JOIN Table T2 ON T2.Account = Phase1.Account AND T2.DateTime = MaxDate

It is still slow but seems to be the fastest way I can find.
Would love to add an index to compare speeds...

What I found was ever faster (bearing in mind this query is used in .NET code) is to do the first select:

SELECT Account, MAX(DateTime) FROM Table GROUP BY Account

And then to loop through the results generating a select / union SQL query:

SELECT TOP 1 Account, MaxDate, Value FROM Table WHERE Account = '<Account1>' AND DateTime = '<maxdate1>'
UNION
SELECT TOP 1 Account, MaxDate, Value FROM Table WHERE Account = '<Account2>' AND DateTime = '<maxdate2>'

I would have thought this would be slower but I guess being able to specify the TOP 1 in each select is faster than the INNER JOIN...

Thanks again for all replies.