1

Here's the table:

firstname |lastname |Gender  | Age
fname1    |Lname1   | male   | 23
fname2    |Lname2   | male   | 22
fname3    |Lname3   | male   | 20
fname4    |Lname4   | female | 19
fname5    |Lname5   | female | 22
fname6    |Lname6   | female | 17

I want to select only 1 value such that when I set gander = male I should get the first and last name of the male with lowest age.

If I want to select the second lowest age person of gender = male I should also get that one, and similarly for female.

jarlh
  • 42,561
  • 8
  • 45
  • 63
user3857870
  • 11
  • 2

3 Answers3

0

try this:

select firstname,lastname  from db where Gender = 'male' order by Age limit 1;
Piotr Rogowski
  • 3,642
  • 19
  • 24
  • select firstname,lastname from db where Gender = 'male' order by Age limit 1; Here it is showing error that ** Incorrect syntax near 'limit'. ** – user3857870 Aug 29 '16 at 09:09
0

For Lowest 

SELECT * FROM `test` WHERE Gender='male' ORDER BY Age ASC LIMIT 0,1;

For Second Lowest

SELECT * FROM `test` WHERE Gender='male' ORDER BY Age ASC LIMIT 1,1;
krunal nerikar
  • 436
  • 4
  • 12
0

If you want to get the second lowest use this:

SELECT firstname,lastname  FROM db WHERE Gender = 'male' ORDER BY Age LIMIT 1, 1;

LIMIT number_rows [ OFFSET offset_value ] the second 1 after limit defines your offset to begin with.

LIMIT doesn't work for oracle (11). In oracle-databases you need to create a temporary table with rowNumber column and perform selection by RowNum.

If you need it for oracle look at this post: Limit Number of Rows

Community
  • 1
  • 1
WPFGermany
  • 1,639
  • 2
  • 12
  • 30