Sql FUNCTION error
Question

How Could i retreive the second highest price room using Min function in sql queries.It's throws an error to me for this queries.How will i retreive?

Hello,

You can try to use an alias to see if it helps. Something like:

select min(r.[Price]) from (SELECT top 2 ... ) r

You should also try to see if the "order by price desc" is making a difference and try to use: " order by {Room}.[Price] desc" instead.

Tell me if it worked out for you.

mvp_badge
MVP

Tiago is twice right.

  1. You can't select from Room because it doesn't exist anymore.
  2. You have to sort by Room.price because that's the name of the attribute.


SELECT min(r.[Price])

FROM (SELECT top 2 {Room}.[Price]

FROM {Room} ...

ORDER BY {Room}.[Price] desc

) r 

should do the trick.


Notice that you don't need to select all fields in the internal query if you are only using price.

By referring a SQL query that I used before I think you can perform that in this way (as well).

SELECT min(PriceXXX) FROM

(SELECT top 2 {Room}.[Price] 'PriceXXX'

... ORDER BY {Room}.[Price] desc) tempTable;

Please, carefully check bold fields, I hope this helps you. :)

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.