Selecting only the maximum values 1

[WillRiley]

Hi,

I have a table with multiple records for each ID. The records all have an effective date & I wish to return only the 75 or so records out of 20,000 that are the most recent for each id...

i.e. for each ID, return the record corresponding to the maximum effective date...

In access, I managed to do this with the following SQL

SELECT dbo_grup.id, Max(dbo_gchg.effective) AS MaxOfeffective
FROM dbo_grup INNER JOIN dbo_gchg ON dbo_grup.rowno = dbo_gchg.rowno_grupgchg_grup
WHERE (((dbo_grup.id)=[dbo_gchg].[id]))
GROUP BY dbo_grup.id;

I have tried a number of ways to achieve similar results in Crystal but with little success.... any advice would be appreciated,

Thanks

 

[GJParker]

What version of Crystal are you using ?

In V9 and above you can base your report on an SQL command and enter the SQL directly.

Are you able to create a view or Stored procedure on your database and report on this rather than from the db tables.

Gary Parker
Systems Support Analyst
Manchester, England

 

[skuhlman]

This type of a statement works in SQL. Perhaps it would work in Access too...



select dbo_grup.id, dbo_gchg.effective, .......
from dbo_gchg A INNER JOIN dbo_grup on A.rowno_grupgchg_grup = dbo_grup.rowno
where dbo_gchg_key in (select max(dbo_gchg_key)
from dbo_gchg B
where A.rowno_grupgchg_grup = B.rowno_grupgchg_grup
and B.effective in (select max(effective)
from dbo_gchg C
where C.rowno_grupgchg_grup = A.rowno_grupgchg_grup))

 

[WillRiley]

GJ Parker,

I am on 8.5.... we do have & intend to upgrade to 9 but it won't be for a little while yet...

skuhlman,

Thanks for the code.... but I can't seem to get the correct SQL syntax for Crystal.... your code may well work in Access, but then so does the code I posted...

If I paste your code into Crystal, it just errors out.... sorry if this is not very helpful feedback!

The current syntax that returns ALL of the records is

SELECT
gchg."effective", gchg."id"
FROM
"pdunity"."dbo"."gchg" gchg
ORDER BY
gchg."id" ASC,
gchg."effective" DESC

I just somehow need to get only the record for each ID corresponding to each IDs latest (or Maximum) effective date...

Hope this is a bit clearer

 

[GJParker]

SELECT
    gchg."id",Max(gchg."effective") as MaxDate
FROM
    "pdunity"."dbo"."gchg" gchg
GROUP BY
   gchg."id"

Gary Parker
Systems Support Analyst
Manchester, England

 

[GJParker]

Meant to say

you didn't say if a view or stored procedure on the database was not possible, as this would make the report easier, but you could try the code above.

Post any error messages if they occur.

HTH

Gary Parker
Systems Support Analyst
Manchester, England

 

[WillRiley]

Gary,

Thanks

But if I post your code into the Show SQL query dialog box, it seems to convert it to

SELECT
    gchg."effective", gchg."id"
FROM
    "pdunity"."dbo"."gchg" gchg

when I run it.... which suggests that i cannot use Max in the Show SQL Query box.... am I being thick... ?

BTW... stored procedure issue... I don't think I have the rights to create a stored procedure on our database... would this sort of thing really help... If so, could you sort of let me know how I'd go about it... I am not great with SQL server admin/development...

Cheers,

Will

 

[vidru]

Group the report on {gchg.id}, then go to Report > Edit Selection Forumla > Group and enter:

{gchg.effective} = Maximum({gchg.effective},{gchg.id})

-dave

 

[GJParker]

If your using SQL Server then I would suggest going down the route of creating a view in the database. This is the same as creating a query in access.

If you haven't got access priveleges to do this your dba should be able to do this for you quite easily.

Let me know if you need further help.



Gary Parker
Systems Support Analyst
Manchester, England

 

[WillRiley]

Dave/Vidru....

Don't want to return all the records & then group them... the results set will be used in another report... hence the need to filter the recordset on selection... thanks anyway

Gary, I will explore the view issue & get back tomorrow... Thanks

 

[AdHoc]

I have version 9 now but if I remember correctly, previous versions will not let you change the first part of what is in the Show SQL Query box. If you change anything before the first WHERE clause CR will change it back.

What you could do is use CR to select the gchg."effective", gchg."id" fields from the table and also use CR to set an alias name for the table, let's call it 'A'. Then open the Show SQL Query box and add your own WHERE clause like what you had in the SQL that you wanted to use.

Of course, it is better to get the DBA to let you create a stored view or procedure in the database, as Gary Parker suggests.

 

[lbass]

Here's an example of syntax in the "Show SQL Query" area (with the where clause written in) that works using the Xtreme database:

SELECT
Employee.`Employee ID`,
Orders.`Order Date`, Orders.`Ship Date`
FROM
`Employee` Employee LEFT OUTER JOIN `Orders` Orders ON Employee.`Employee ID` = Orders.`Employee ID`
WHERE
Orders.`Order Date` = (SELECT MAX(AKA."Order Date") FROM Orders AKA where AKA."Employee ID" = Orders."Employee ID")
ORDER BY
Employee.`Employee ID` ASC, Orders.`Order Date` DESC

You might be able to adapt your query using this example...

-LB

 

[WillRiley]

LB,

I am assuming that AKA is a second alias table of Orders... ? Is this right...

On that basis I added another alias of gchg, calling it gchg_1 and used what I thjought was the amended SQL following your suggested syntax...

SELECT
gchg."effective", gchg."id"
FROM
"pdunity"."dbo"."gchg" gchg
WHERE
gchg."effective" = (SELECT MAX(gchg_1."effective") FROM gchg_1 WHERE gchg."id" = gchg_1."id")
ORDER BY
gchg."id" ASC,
gchg."effective" DESC


It bugs out giving me "Invalid Object Name gchg_1 - Error detected by database DLL"

Have I done something daft ?

 

[WillRiley]

Hmmmm,

Strangely enough it works in SQL Analyzer

SELECT
gchg.effective, gchg.id
FROM
gchg
WHERE gchg.effective =
(SELECT MAX(AKA.effective) FROM gchg AKA
WHERE AKA.id = gchg.id)
ORDER BY
id ASC,
effective DESC

which is cool.... not sure what is wrong with my Crystal syntax thos....

 

[WillRiley]

Sorry guys.... It was my syntax... I forgot the bit that said

FROM gchg AS AKA

The following now works in SQL & Crystal

SELECT
gchg."effective", gchg."id"

FROM
"pdunity"."dbo"."gchg" gchg        

WHERE
gchg."effective" = (
   SELECT MAX(AKA."effective")
   FROM gchg AS AKA
   WHERE AKA."id"= gchg."id")        

ORDER BY
gchg."id" ASC,
gchg."effective" DESC

Thanks for all your help guys.

Will.

BTW - Gary, I am going to set this up as a stored View too, but in the meantime this has solved quite a tricky issue for me so thanks all...

 

[synapsevampire]

Aside from manually editing the SQL, which will lose your ability to faithfully use the CR interface to modify the SQL, you can use a SQL Expression to create the subquery, and then reference the subquery in the Report->Edit Selection Formula->Record, reference the sql expressiona and Crystal will generate and pass the appropriate sql (though I believe that it would create a Having Clause).

This is a much more flexible means of using nested queries.

-k

 

[WillRiley]

Cheers,

Where do I place the subquery SQL expression? Sorry if that's a bit basic...

Will