Select Second Highest Value (1 Viewer)

[ejstefl]

Hello,

I am looking for a query to select the second highest value (in this case a date) from a table.

For example, if I had the following dates:
9/1/06
9/2/06
9/3/06
9/4/06

I would want the results of my query to be 9/3/06.

My table is called tblEquityFunds and the field I'm looking to select is called Added. I have the following query, which I think works, but it seems like an awfully goofy way to do it:

SELECT DISTINCT TOP 2 Max(tblEquityFunds.Added) AS MaxOfAdded
FROM tblEquityFunds
WHERE tblEquityFunds.Added Not In (Select Max(Added) FROM tblEquityFunds))

Is there a more elegant solution?

 

[cuttsy]

I would have gone down a similar rout.
Why do you select the TOP 2 when you only want the one value?

 

[raskew]

Hi -

Having spent 1/2 hour (unsucessfully) trying to refine the solution, have to say that's a good problem and you've come up with a working solution. Go with it!

Bob

 

[ejstefl]

I would have gone down a similar rout.
Why do you select the TOP 2 when you only want the one value?

LOL - I have no idea! Maybe that's why I thought it looked goofy. I sort of just tried a bunch of things until it worked. I took out the Distinct and changed it to top 1 and it looks good... thanks for pointing that out!

 

[ejstefl]

Hi -

Having spent 1/2 hour (unsucessfully) trying to refine the solution, have to say that's a good problem and you've come up with a working solution. Go with it!

Bob

Thanks for your efforts! I wasn't sure if there was an accepted way to do this or not. The reason I need to do this is to compare the newest data with the second newest data for changes... never had to do that before.

Thanks again!

 

[raskew]

Hey -

It works (accomplishes the mission). Who cares if it's 'goofy'? It works, and that's the bottom line. We're talking milliseconds here. I'm saving your solution in my archieves. Great programing.

Bob