UKSystemSupport
asked on
LINQ statement using PARTITION BY
Hi Experts,
I have the sql query below which returns locate_ids adding a letter at the end if the locate_id shows up multiple times (ie 133A,133B,133C). I need to generate this query using LINQ (C#).
Does LINQ have an equivalent to the PARTITION BY statement or can this be accomplished some other way?
I have the sql query below which returns locate_ids adding a letter at the end if the locate_id shows up multiple times (ie 133A,133B,133C). I need to generate this query using LINQ (C#).
Does LINQ have an equivalent to the PARTITION BY statement or can this be accomplished some other way?
select l.locate_id ||
case when row_number() over (partition by locate_id order by locate_id) > 0 then
chr(64 + row_number() over (partition by locate_id order by locate_id))
end locate_id
from locate l
inner join locate_schedule ls
on l.locate_schedule_id = ls.locate_schedule_id
inner join locate_schedule ls2
on ls.locate_schedule_id = ls2.orig_locate_schedule_id or ls.orig_locate_schedule_id = null
ASKER
Hi Marco,
Thanks for the response.
The article is pretty helpful, however not quite what I am looking for. Based on that example I am able to get the max, min, or even the count for a given group of records returned. I also found a similar post here:
http://efreedom.com/Question/1-3100530/Generate-SQL-COUNT-PARTITION-ColumnName-LINQ-SQL
What I would need is to return some kind of index (the letter) incrementing within each group. Ie: if ID 133 shows up 3 times then have it show up as 133A, 133B, 133B.
Thanks for the response.
The article is pretty helpful, however not quite what I am looking for. Based on that example I am able to get the max, min, or even the count for a given group of records returned. I also found a similar post here:
http://efreedom.com/Question/1-3100530/Generate-SQL-COUNT-PARTITION-ColumnName-LINQ-SQL
What I would need is to return some kind of index (the letter) incrementing within each group. Ie: if ID 133 shows up 3 times then have it show up as 133A, 133B, 133B.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks yamir2k!
This is what I was looking for.
Just to note that the Select extension method will only work with Lists.
This is what I was looking for.
Just to note that the Select extension method will only work with Lists.
This article shows some examples. Search for 'partition by' on the page (around halfway down on the page. http://smehrozalam.wordpress.com/tag/linq/
Kind Regards
Marco
Open in new window