Last post Aug 14, 2008 09:29 AM by robroe
Aug 12, 2008 12:07 PM|robroe|LINK
One of the fields in my tables is Ranking, it is basically there to promote items to the top of lists (other items are sorted alphabetically). Now it may well be that my logic is flawed here, but in order to make things easiest for the people who are going
to use it I made a ranking of 1 be the highest value (will show up at the top) and 2 the second (will show up second) and so on.
The problem I now have is that when I sort by ranking it sorts them by the un-ranked items first (null), then any ranked 1, any ranked 2 etc etc. Or the opposite way around if sorted desc. What I would ideally like is for the items to be sorted 1, 2, 3,
4... and then null.
Essentially I would like the equivalent of a SQL ORDER BY ISNULL(Ranking, 255). I've tried implementing the GridViews on_sorting method but I can't quite figure out how I might do this. I've tried adding an new Property to the Entity but that didnt work
due to it not knowing how to create the SQL for that property, and I tried setting the e.SortExpression to ISNULL(..) but was told the Entity didnt have this method. I can totally understand why I got these errors, but does anyone know if anything like this
Aug 12, 2008 02:12 PM|etariq|LINK
did u try this
select ISNULL(Ranking, 255) as ranking ....
order by ranking
Aug 12, 2008 02:21 PM|sjnaughton|LINK
did u try thisselect ISNULL(Ranking, 255) as ranking ....
order by ranking
I think Robroe is after a Dynamic Data/Linq to SQL solution [:D]
Aug 12, 2008 02:41 PM|sjnaughton|LINK
Hi Robroe, I've had a thought why not create a Calculated Column on the table is' self and then sort on that instead of the actual column, or alternativly set a default value of 255 on the Ranking column so it always has a value of 255 unless
set by the user.
Then you could just use the LinqDataSource's AutoGenerateOrderByClause and
OrderByParameters to sort the grid.
Hope this helps [:D]
Aug 12, 2008 05:25 PM|sjnaughton|LINK
The computed column would look like this
[SortRank] AS (isnull([Ranking],(255)))
See the full listing for creating the table dbo.Test:
/****** Object: Table [dbo].[Test] Script Date: 08/12/2008 22:21:31 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[Test](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Test] [nvarchar](50) NOT NULL,
[Ranking] [int] NULL,
[SortRank] AS (isnull([Ranking],(255))),
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]
) ON [PRIMARY]
Sort on null values
Aug 14, 2008 09:29 AM|robroe|LINK
Thanks for your help, yep I was looking for a LINQ solution, but it might well be that its just because my db is counter intuative that I'm having these problems. I'm starting to think that it might be better to set the default value of the actual db column
to be 255 or some high number rather than doing the whole isnull thing all over the place. I should be able to shield the user from knowing anything about this by using a customised slider user control or similar.
Thanks again, if there was an assisted/partial answer button I would give you that, but as there isn't I'll be generous and mark it as answer ;)