Help On Query using Order by and Group by

Posted by Vijayar under ASP.NET on 12/27/2011 | Points: 10 | Views : 1336 | Status : [Member] | Replies : 2
Hi
The Structure Of Table is Thought ID,Thought,Language
I am saving thought in 3 different languages .I need to display the latest thought entered in 3 different languages in a page at a time.How to do this.Please Help Me.It's Very urgent

vijaya


Responses

Posted by: Ivlnr on: 12/27/2011 [Member] Starter | Points: 25

Up
0
Down
Hi,

Is this you are looking for?

select top 1 thought,language from abc_tbl
where language='A'
order by thoughtid desc
union
select top 1 thought,language from abc_tbl
where language='B'
order by thoughtid desc
union
select top 1 thought,language from abc_tbl
where language='C'
order by thoughtid desc


Vijayar, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Suvendu on: 12/27/2011 [Member] Starter | Points: 25

Up
0
Down
if you are sure that the no of language will not increase in future then you can try the Ivlnr said above, But if are not sure then you have to find it dynamically and for this you can use the the bellow query--

SELECT ThoughtId, Thought, Language

FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY Language ORDER BY ThoughtId DESC) AS TopThought,* FROM Thoughts
) T
WHERE T.TopThought =1


hope, it will hep you..
If i am missing something then plz revert me back..
If it solves your problem mark it as answer.

Thanks

Vijayar, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response