I have two tables, one is called (questions), the second one (answers).

questions columns are (ID,questionTitle)

answers columns are (ID,questionID,answer, answerDate)

I use this query to load data: "SELECT q.questionTitle,COUNT (a.ID),a.answerDate FROM questions q LEFT JOIN answers a ON q.ID=a.questionID"

the query is easy, but my problem which I can't solve is how can I fetch the data ordered by the column answerDate, I mean I want the first record to be the one which has the most recent answer and so on.

Did you try this?

SELECT q.questionTitle,COUNT (a.ID),a.answerDate

FROM questions q LEFT JOIN answers a ON q.ID=a.questionID

ORDER BY a.answerDate DESC

Just add to the query ORDER BY a.AnswerDate DESC to order by the answer date. Also, your query should probably be

SELECT q.questionTitle,COUNT (a.ID) OVER(partition by q.Id) as TotalAnswers,a.answerDate

FROM questions q LEFT JOIN answers a ON q.ID=a.questionID

ORDER BY a.AnswerDate DESC

Post some sample data and tell what is the outputs you want.

thanks, but unfortunately your code fetch duplicated records, how can I bring only  unique q.ID???

• Edited by HSawa 14 hours 36 minutes ago

Can you post some data and desired output?

;with cte as (select q.*, a.answer, a.answerDate, row_number() over (partition by q.Id order by a.AnswerDate DESC) as Rn, COUNT(a.Id) OVER (partition by q.Id) as TotalAnswers from Questions q LEFT JOIN Answers a on q.Id = a.QuestionId)

select QuestionTitle, Answer as LastAnswer, AnswerDate as LatestDate, TotalAnswers

from cte where Rn = 1

ok, here is a sample

questions table:

ID       title

1           this is first question

2           title of question number 2

answers table:

ID   qID  answerDate

1     2      Jun/2/2000

2     2      Jun/3/2000

1      1     Jun/4/2000

so I need the result to be like this:

first record:  title of question number 2

second record: this is first question

because the second question was answered before the first one

you see all forums do the same thing, If you answer an old post it willl jump to the top of the list

• Edited by HSawa 14 hours 23 minutes ago

select q.QuestionTitle, count(a.Id) as TotalAnswers, max(a.AnswerDate) as LastDate

from Questions Q LEFT JOIN Answers a on Q.Id = A.QuestionId

GROUP BY q.Id, q.QuestionTitle

ORDER BY LastDat

DECLARE @Questions TABLE (
	ID INT,
	QuestionTitle VARCHAR(100)
)
DECLARE @Answers TABLE (
	ID INT,
	QuestionID INT,
	Answer VARCHAR(100),
	AnswerDate datetime
)

INSERT INTO @Questions VALUES 
(1, 'this is first question'), (2, 'title of question number 2')

INSERT INTO @Answers (ID, QuestionID, AnswerDate)
VALUES (1, 2, '2000-06-02'), (2, 2, '2000-06-3'), (1, 1, '2000-06-04')

SELECT QuestionID, QuestionTitle, AnswerDate
FROM
(
SELECT a.QuestionID, q.QuestionTitle, a.AnswerDate, ROW_NUMBER() OVER (PARTITION BY a.QuestionID ORDER BY a.AnswerDate) AS Ranked
FROM @Answers AS a
INNER JOIN @Questions AS q ON q.ID = a.QuestionID
) AS Temp
WHERE Ranked = 1
ORDER BY AnswerDate ASC

Your output doesn't match the original description. You wanted the latest answer on top, but in the specified output you wanted the earliest question on top.

Naomi N,

Thanks a lot, you solved my problem.

select q.QuestionTitle, count(a.Id) as TotalAnswers, max(a.AnswerDate) as LastDate

from Questions Q LEFT JOIN Answers a on Q.Id = A.QuestionId

GROUP BY q.Id, q.QuestionTitle

ORDER BY LastDate DESC

>> I have two tables, one is called (questions), the second one (answers). <<
Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your datA. You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data (https://xkcd.com/1179/). We need to know the data types, keys, DRI and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL. 

Now we have to guess from you narrative how they are related. In RDBMS, a relationship would be in a table by itself. But it not in your narrative! There is no such thing as a generic, universal id in RDBMS; it has to identify something in particular. Identifiers cannot switch from a question to an answer to an automobile. There are noobs who think that id means IDENTITY and actually use it in code! So very wrong. 

Here is a guess at what you might have posted.

CREATE TABLE Questions
(question_id CHAR(5) NOT NULL PRIMARY KEY, 
 question_title VARCHAR(15) NOT NULL
 question_txt VARCHAR(500) NOT NULL); 

CREATE TABLE Answers
(answer_id CHAR(5) NOT NULL, 
 question_id CHAR(5) NOT NULL
  REFERENCES Questions (question_id), 
 PRIMARY KEY (answer_id, question_id), 
 answer_date DATE NOT NULL, 
 answer_txt VARCHAR(500) NOT NULL); 

>> .. my problem which I can't solve is how can I fetch the data ordered by the column answer_date, I mean I want the first [sic] record [sic] to be the one which has the most recent answer and so on. <<

Rows are not records; tables are not sorted. That is a file system. You want to create a cursor with an ORDER BY clause? How do you want to show an unanswered question? It will have no date! Wish we had sample data ..


SELECT Q.question_title, A.answer_date, 
       COUNT(A.answer_id) AS answer_cnt
  FROM Answers AS A, Questions AS Q 
 WHERE Q.question_id = A.question_id
 GROUP BY Q.question_title, A.answer_date
 ORDER BY Q.question_title, A.answer_date;