CODEWITHSUNDEEP
sqlms-accessmaxmin
Gabriel Leong
Gabriel Leong

Reputation: 55

How to return the minimum and maximum record in one resultset (Microsoft Access)

I want to show the least and most expensive movie from a table. But the sql below does not run in Microsoft Access. How do I fix it so it returns both the min and max values in 1 result table?

SELECT  Cost,Movie_Title
FROM    INVENTORY
WHERE   Cost IN 
(
   SELECT max(Cost) as Maximum_Cost, min(Cost) as Minimum_Cost
   FROM INVENTORY
);

Upvotes: 2

Views: 1206

Answers (2)

HansUp
HansUp

Reputation: 97111

SELECT Movie_Title, Cost
FROM INVENTORY
WHERE
       Cost=DMin("Cost","INVENTORY")
    OR Cost=DMax("Cost","INVENTORY");

Upvotes: 2

Andiih
Andiih

Reputation: 12413

if you want the answer as two rows use Union to attach the results of a minimum and maximum queries together.

Upvotes: 1

Related Questions