CODEWITHSUNDEEP
sqlms-accessms-access-2010
Dexter.Patterson
Dexter.Patterson

Reputation: 3

Having more than one condition in Access using SQL

I want FirstName and LastName of the tallest female in the table.

image of table

I tried using:

select FirstName, LastName
from actors
where  height = (select max(height) from actors) and gender = 'F';

but it doesn't work. Can someone show me how it's done?

Upvotes: 0

Views: 98

Answers (3)

Narasimha Maiya
Narasimha Maiya

Reputation: 1029

I think this will work.

select FirstName, LastName
from actors
where  height = (select max(height) from actors where gender = 'F') and gender = 'F';

Upvotes: 0

spencer
spencer

Reputation: 169

Although my sql is terrible, i think this can be solved in selecting what you want from subsets.
Select everything first

SELECT a.FirstName, a.LastName, a.height FROM actors AS a WHERE a.gender = 'F';

Then select the max(height) from that subset with additional fields you want.

SELECT m.FirstName, m.LastName, max(m.height) FROM (SELECT.. AS a) AS m

Then filter out what you want if you need to

SELECT f.FirstName, f.LastName FROM (SELECT..(SELECT..AS a) AS m) AS f

All together looks like this

SELECT f.FirstName, f.LastName FROM
 (SELECT m.FirstName, m.LastName, max(m.height) FROM
   (SELECT a.FirstName, a.LastName, a.height FROM
     actors AS a WHERE a.gender = 'F') AS m
  )as f;

I admit it's convoluted, but it does work in my mysql ;)

Edit: Many apologies for the error, but I thought this was regarding MySQL. It is my first post and I guess I got a little trigger happy. I believe subsets are basic SQL though.

Upvotes: 0

Joel
Joel

Reputation: 21

select * from <TABLE NAME> where gender = 'F'
and height = (SELECT max(height) from <TABLE NAME> where gender = 'F')

Tested on Access 2013.

Upvotes: 2

Related Questions