MAX date in subselect, can't use group select 1

[mwhalen]

Hi there, I'm trying to hardcode the SQL for a Crystal report. I want to return the row that has the max effective date (I need to get around using the group select function that Crystal offers which is why I want to hardcode).

I've tried and I just can't get it. Is there a way to use a subselect, i.e. EXISTS with the MAX function in that?

Here is the basic SQL - some of the examples I see have the joins but I have joined the tables already in the designer. Thanks!


SELECT
PS_ADDRESSES."EMPLID", PS_ADDRESSES."EFFDT"
FROM
"SYSADM"."PS_ADDRESSES" PS_ADDRESSES
WHERE
PS_ADDRESSES."EMPLID" LIKE '______002'
ORDER BY
PS_ADDRESSES."EMPLID" ASC

 

[rscero]

If I'm reading your question correctly, I believe the code below should work...

SELECT
PS_ADDRESSES."EMPLID", PS_ADDRESSES."EFFDT"
FROM
"SYSADM"."PS_ADDRESSES" PS_ADDRESSES
WHERE
PS_ADDRESSES."EMPLID" LIKE '______002' AND
PS_ADDRESSES."EFFDT" = (SELECT MAX(A.EFFDT) FROM PS_ADDRESSES A WHERE A.EMPLID LIKE '______002')
ORDER BY
PS_ADDRESSES."EMPLID" ASC

 

[rscero]

One thing I forgot to mention, if the table contains records with identical effective dates (no time stamps) and the Like clause is going to return more than one value, this code will not work. The sub-select has to return a single value from the table.

 

[Naith]

Like rscero says, the code he offers is on the nose. Except that you will have to exchange the 'Like' operand for '=' in the subselect.

Naith

 

[mwhalen]

Hi, I got it to work in that table but then I had to revise my report and now it won't work. Can you give me any further detail as to the logic of it and if I do use that code in another report where the table structure might be slightly different, what are the key things I have to change? THANKS SO MUCH!!!

 

[Naith]

Perhaps I don't really understand the question, but you can use that SQL anywhere. All you would change would be the dynamic areas; i.e. the table names/field names/values.

If you're trying to ascertain why the report is falling over, you need to provide a more specific diagnosis than "it won't work". What exactly happens?

As for logic, all the subselect does is say: "If the main select fetches more rows than one for each EMPLID, then I want the row with the most recent date for that EMPLID - and so on, until we run out of EMPLIDs."

I hope this helps, but if not, give us another shout.

Naith

 

[mwhalen]

I'm just trying it now - I think it's my links in my joins that are throwing me off

 

[mwhalen]

got it - HOWEVER - what I seem to be getting is all those in the database where the max effective date is June 4, 2002. I know there are id's in there where their max effective dates are earlier. That would say to me that I'm getting THE MOST RECENT addresses (and their id's) in the entire databse - NOT the most recent addresses for the id's. Does that make sense?

 

[Naith]

It exactly makes sense.

The problem isn't you - it's the query you've been given which is wrong. Sorry - I really should have noticed earlier.

Try this:

SELECT
PS_ADDRESSES."EMPLID", PS_ADDRESSES."EFFDT"
FROM
"SYSADM"."PS_ADDRESSES" PS_ADDRESSES B
WHERE
PS_ADDRESSES."EMPLID" LIKE '__002' AND
PS_ADDRESSES."EFFDT" =
(SELECT MAX(A.EFFDT) FROM PS_ADDRESSES A
WHERE B.PRIMARY_KEY = A.PRIMARY_KEY)
--PrimaryKey = your own unique identifier
ORDER BY
PS_ADDRESSES."EMPLID" ASC

Naith

 

[Naith]

...and "Order by" should be "Group by"

 

[mwhalen]

"Order by" is what is put in the SQL when I group in my Crystal Designer on my ID?

 

[Naith]

I thought you were re-writing your own SQL. Crystal may pre-empt your needs with an ORDER BY, but if you want maximum dates for each EMPLID, you want to GROUP BY.

Naith

 

[mwhalen]

I just want to rewrite part of my SQL for the subselect - I need to leave the grouping on my Crystal Designer - will it still work?