MySQL - SubQuery - Error in your SQL Syntax

Question

I'm pretty new to SQL, so I'll try to keep it simple as to what I'm trying to do.

I have a system which I'm looking to select messages from, starting with the most recent first, select a maximum of 5 pieces of data, then after that resort them with the latest 'time' column last in order for them to display properly.

Here's the syntax I'm using:

SELECT * FROM messages WHERE sender = '$uid' AND reciever = '$new_user_id' 
OR reciever = '$uid' AND sender = '$new_user_id' ORDER BY id ASC 
FROM (SELECT * FROM messages ORDER BY time DESC)

And here's the error that I'm getting:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM (SELECT * FROM messages ORDER BY time DESC)' at line 1

I understand that I'm getting something wrong here, but as SQL isn't really my thing, I haven't a clue where to turn.

A little help would go to great causes, I've been banging my head on the wall for hours.

Thanks.

Just edited it apparently I seem to of missed out a ORDER BY before the second FROM. — Alex Connolly
– Alex Connolly, Commented Apr 2, 2012 at 18:24
You still are using two FROM statements. Try to break the query into sub-parts(as I have done in my answer) it helps you know the logic better. ;) — hjpotter92
– hjpotter92, Commented Apr 2, 2012 at 18:27

hjpotter92 · Accepted Answer · 2012-04-02 18:19:56Z

2

Try this:

SELECT * 
  FROM (SELECT * FROM messages ORDER BY time DESC LIMIT 5)
  WHERE ( sender = '$uid' 
    AND reciever = '$new_user_id' )
    OR ( reciever = '$uid' 
    AND sender = '$new_user_id' )
  ORDER BY time ASC;

EDIT Edited to resort with ascending time values in last 5 items inserted.

answered Apr 2, 2012 at 18:19

hjpotter92

81.1k36 gold badges148 silver badges188 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alex Connolly Over a year ago

Could you check out the query again, sorry I made a mistake in the first one as I had two versions of the query.

Alex Connolly Over a year ago

Thanks, this should help me get it sorted!

Churk · Accepted Answer · 2012-04-02 18:26:32Z

0

SELECT * FROM (
    SELECT *
    FROM messages 
    WHERE
    (sender = '$uid' AND reciever = '$new_user_id')
    OR (reciever = '$uid' AND sender = '$new_user_id')
    ORDER BY time asc
    LIMIT 5) AS a ORDER by a.time DESC

answered Apr 2, 2012 at 18:26

Churk

4,6475 gold badges25 silver badges40 bronze badges

Comments

thezboe · Accepted Answer · 2012-04-02 18:21:17Z

0

No need for the subquery it seems. Take off the subquery and tack on ORDER BY time DESC LIMIT 5.

answered Apr 2, 2012 at 18:21

thezboe

5521 gold badge5 silver badges13 bronze badges

1 Comment

Alex Connolly Over a year ago

Could you check out the query again, sorry I made a mistake in the first one as I had two versions of the query.

Collectives™ on Stack Overflow

MySQL - SubQuery - Error in your SQL Syntax

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related