Select top 1 doesn't work with ORDER BY

[Bullsandbears123]

I have this SQL statement....

SELECT TOP 5 table1.myvalue AS myvalue223
FROM table1
WHERE table1.appdate<#6/10/2004#
ORDER BY table1.appdate;

But is returns more then the top 5, if I remove the order by statement it works fine.

What am I doing wrong? Please HELP I'm really in a jam!
thank you!!!!

 

[scriverb]

This is because it selects the TOP 5 after the Order By clauses executes. You see it performs the TOP 5 on the records after they are sorted and if you check your resulting recordset you will probably see 5 distinct appdates.

Do you want it to just select the records TOP 5 in the original order and then sort them by appdate?

If so try this:

SELECT A.myvalue AS myvalue223
FROM table1 AS A 
WHERE A.myvalue IN (SELECT TOP 5 B.myvalue 
FROM table1 AS B 
WHERE B.appdate<#6/10/2004#) 
ORDER BY A.appdate;

Let me know if this works for you.


[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[dhookom]

The TOP anything will return more records if there are duplicate values of the ORDER BY field.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Bullsandbears123]

SELECT qryExpPosUndHistory_on.MarketValue AS Expr2, nz((SELECT Marketprice FROM qryExpPosUndHistory_on AS T WHERE T.Symbol=qryExpPosUndHistory_on.symbol and T.appdate<qryExpPosUndHistory_on.appdate ORDER BY appdate DESC,0) AS Expr1
FROM qryExpPosUndHistory_on;

This is my actual SQL and when I run it, it runs for a little bit I see it building the table and it states...

"At most only one record can be returned by this sub query"
I am trying to gram the market value of one day and the market value of the previous day, but I keep getting an error. The query looks like it's running fine, but at the very end I get the error. Any ideas?

 

[Bullsandbears123]

One more thing, the source query has duplicate appdate values.

 

[scriverb]

I don't see anything that has to do with the TOP 5 problem that you stated originally. Is this code you provided the results of trying what I posted or is it what you had before you tried the TOP 5?

Please explain.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Bullsandbears123]

I thought the problem was because of the Select TOP issue. But it was just a guess. Now I have no idea why my code won't work. Sorry, I usually simplify my problems on the board. it make things easier. But in this case it made things harder.

 

[scriverb]

As the nested subquery is supposed to only return one record because you are creating a single field, there is probably a situation in this subquery that is returning multiple records. ACCESS Jet doesn't know what to do with multiples in this siatuion.

SELECT qryExpPosUndHistory_on.MarketValue AS Expr2, [/red]nz((SELECT Marketprice FROM qryExpPosUndHistory_on AS T WHERE T.Symbol=qryExpPosUndHistory_on.symbol and T.appdate<qryExpPosUndHistory_on.appdate ORDER BY appdate DESC;),0) AS Expr1[/red]
FROM qryExpPosUndHistory_on;

Hopefully this will put on the right track.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Bullsandbears123]

Sorry, I'm an idiot, I forgot to include the "top" statement in the sql. My sql in the program has it, just not the post. Sorry, that must be why you did not see the connection. My fault.

 

[scriverb]

The error you are getting only has to do with the RED code below. Do you understand that?

SELECT TOP 5 qryExpPosUndHistory_on.MarketValue AS Expr2, [red]nz((SELECT Marketprice FROM qryExpPosUndHistory_on AS T WHERE T.Symbol=qryExpPosUndHistory_on.symbol and T.appdate<qryExpPosUndHistory_on.appdate ORDER BY appdate DESC;),0) AS Expr1[/red]
FROM qryExpPosUndHistory_on;

This problem has to be resolved before we can proceed. What value do you want as your second column. That subquery can only return one row for it to work correctly. What is the logic for selecting the correct MarketPrice?

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Bullsandbears123]

Yes I realized the sub query was my problem.

The market price should be used from the max date which is < the current records date.

The red area SQL should start with SELECT TOP 1 MarketPrice...

 

[scriverb]

The Top 1 of the subquery is still the problem. You see there are multiple dates that fall into the Top 1 which it can't handle as a subquery for a single field in a single row. We need to figure a way to select the max date and group by that date so that only one row is returned in the subquery. I have to leave now and i won't be able to work on this until later this evening or tomorrow.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Bullsandbears123]

T.Symbol=qryExpPosUndHistory_on.symbol should tell it which one of the date to choose from.

I think I got it working, but I'm not exactly sure what I changed. I've been working on this thing all day.

THANK YOU FOR YOUR HELP!
I do appreciate the time and assistance.