How to filter the records based on row number

Hi All,

           When i try to filter record using below query  based on row number systems throws the following error


Thanks

R.karthik

Hi Karthik,

What are you trying to achieve? can you try to explain a bit you data model so we can help you better?

Regards,

Marcelo

karthik R wrote:

Hi All,

           When i try to filter record using below query  based on row number systems throws the following error


Thanks

R.karthik


Hello Karthik, 

You can test that query? 

What is you output? 

Marcelo Ferreira wrote:

Hi Karthik,

What are you trying to achieve? can you try to explain a bit you data model so we can help you better?

Regards,

Marcelo


Hi Marcelo,

                     Actually I need to get unique record so created a row number for each record by grouping duplicate column value so if I fetch first row I will get unique record but I can't able to apply filter by assigning rownumber=1


Hi Karthik,

I still didnt understand what you want to accomplish. but from what you said you can just use an aggregate and use the grouping functionality.

So I can give you a better answer show me your data model and example of records in each table.

Regards,

Marcelo

Hi Karthik, 

Check out my solution below

Ensure you have an Output structure matching the attributes you are returning.




Amal Raj wrote:

Hi Karthik, 

Check out my solution below

Ensure you have an Output structure matching the attributes you are returning.




Hi Amal,

              I tried similar to your logic but its not working for me since i am have joined two table in second select list


SELECT t.* from
(select row_number()over(partition by {Player}.[Id] order by {Player}.[Id]) as rownum,
{Player}.*,{PlayerRanking}.* from {Player},{PlayerRanking}
where {Player}.[Id]={PlayerRanking}.[PlayerId]) t
where t.rownum=1

 it throws the following error


Hello Karthik,

I believe the ID column is present in both the tables and this is the reason for the error. 

Instead of SELECT t.*, use SELECT T.columnName explicitly. 

Also creating a structure with all the column names you need will make it clear. 



T is the output of row number and all columns of entities "Player" and "PlayerRanking". Both entities might have an attribute named "Id". That's the error motive