none
Incorrect syntax

    Question

  • I try to run this queriy

    select first_name, last_name, age
    from student
    where student.age = (select max(student.age) from student
    and student.student_type = 'Overseas Student');

    but i'm getting error

    Msg 156, Level 15, State 1, Line 4
    Incorrect syntax near the keyword 'and'.

    • Moved by Bill_Stewart Monday, November 18, 2013 4:50 PM Move to more appropriate forum
    Monday, November 18, 2013 4:44 PM

Answers

  • I'm not sure what you want here.

    If it's only Overseas Students whose age is equal to the maximum age of all students, then

    select first_name, last_name, age
    from student
    where student.age = (select max(s1.age) from student s1)
    and student.student_type = 'Overseas Student';

    If it's all students whose age is equal to the maximum age of all Overseas Students, then

    select first_name, last_name, age
    from student
    where student.age = (select max(s1.age) from student s1
    where s1.student_type = 'Overseas Student');

    Tom

    Monday, November 18, 2013 5:14 PM
  • Hi,

    Not sure what is the requirement but seems the query can be as below:

    select first_name, last_name, age
    from student
    where student.age = (select max(student.age) from student
    where student.student_type = 'Overseas Student');


    Thanks and Regards Alankar Chakravorty MCITP Database Administrator SQL Server 2008 MCITP Database Administrator SQL Server 2005

    Monday, November 18, 2013 5:20 PM

All replies

  • I'm not sure what you want here.

    If it's only Overseas Students whose age is equal to the maximum age of all students, then

    select first_name, last_name, age
    from student
    where student.age = (select max(s1.age) from student s1)
    and student.student_type = 'Overseas Student';

    If it's all students whose age is equal to the maximum age of all Overseas Students, then

    select first_name, last_name, age
    from student
    where student.age = (select max(s1.age) from student s1
    where s1.student_type = 'Overseas Student');

    Tom

    Monday, November 18, 2013 5:14 PM
  • Hi,

    Not sure what is the requirement but seems the query can be as below:

    select first_name, last_name, age
    from student
    where student.age = (select max(student.age) from student
    where student.student_type = 'Overseas Student');


    Thanks and Regards Alankar Chakravorty MCITP Database Administrator SQL Server 2008 MCITP Database Administrator SQL Server 2005

    Monday, November 18, 2013 5:20 PM
  • Hi, Not sure what you were trying to do should write the query either

    select first_name, last_name, age
    from student
    where student.age = (select max(student.age) from student
    where student.student_type = 'Overseas Student');

    Or

    select first_name, last_name, age
    from student
    where student.age = (select max(student.age) from student)
    and student.student_type = 'Overseas Student';

    Hope this helps.............


    Ione

    Tuesday, November 19, 2013 9:40 PM
  • Count your WHEREs. Do not use the constantly changing "age". Use table aliases. Do you really have only one Student, as you said??  Here is one correction:

    SELECT S1.first_name, S1.last_name, S1.birth_date
      FROM Students AS S1 
     WHERE S1.birth_date
           = (SELECT MIN (S2.birth_date) 
               FROM Students AS S2
             WHERE S2.student_type = 'Overseas Student');

    But you might have meant:

    SELECT S1.first_name, S1.last_name, S1.birth_date
      FROM Students AS S1 
     WHERE S1.birth_date
           = (SELECT MIN (S2.birth_date) 
               FROM Students AS S2)
      AND S1.student_type = 'Overseas Student');

    See why we use table aliases? 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, November 19, 2013 10:25 PM