CODEWITHSUNDEEP
mysqlsqlduplicates
Michael Samuel
Michael Samuel

Reputation: 3920

Select next record with the same id

I have a sample table for answers with the following rows. The id could be duplicate as it's joined with questions table based on the id (i.e a question could have multiple answers)

answers.id | answers.username | answer | date
   1       |       mikha      | you    | 2013-01-01 00:00:00   
   1       |     maricela     | really | 2013-01-01 00:00:00 
   1       |        guy       | rock   | 2013-01-01 00:00:00

Is there a query I can use to select all rows after the answer by "maricela"?

Something similar to:

SELECT * FROM answers WHERE id = 1 AND answers.username > 'maricela'

But the above will return 0 rows as doing it like above will select alphabetically and guy alphabetically is before maricela.

Is there any method to select row after row with value(s)? Row after ID=1 and USERNAME=mariclea?

Upvotes: 0

Views: 497

Answers (3)

Mariappan Subramanian
Mariappan Subramanian

Reputation: 10073

This will simply do,

SELECT b.* FROM table1 b WHERE b.id> 
(SELECT a.id FROM table1 a WHERE a.username='maricela'
and a.answer_id=1)

Live SQL Demo

Upvotes: 0

John Farrelly
John Farrelly

Reputation: 7459

I would strongly suggest you add a unique id in your answers table, and have a new column that is used to join to the questions table:

question_id |  id | username | answer | date
    1       |   1 | mikha    | you    | 2013-01-01 00:00:00   
    1       |   2 | maricela | really | 2013-01-01 00:00:00 
    1       |   3 | guy      | rock   | 2013-01-01 00:00:00

Then you could use:

SELECT * FROM answers WHERE id > (
    SELECT id FROM answer WHERE username = 'maricela' and question_id = 1
)

Upvotes: 2

pianoforte
pianoforte

Reputation: 11

something popped up in my mind: if your concept of "after" is related to time (i.e. answers inserted after "maricela") then you can consider adding DATE/TIME column for the answers table. This way you would make sure that some answers are given later than others

Upvotes: 0

Related Questions