I have a table that look like this:
CREATE TABLE [dbo].[Table](
[IDRow] [int] NOT NULL,
[Revision] [int] NOT NULL,
[EntryDate] [datetime] NULL,
[UserID] [int] NULL,
[Title] [nvarchar](100) NULL,
[TypeID] [nvarchar](10) NULL,
[Tags] [nvarchar](max) NULL,
[PriorityID] [nvarchar](10) NULL,
[DeadlineDate] [datetime] NULL,
[Description] [nvarchar](max) NULL,
CONSTRAINT [PK__Table__A6C7A1C4145C0A3F] PRIMARY KEY CLUSTERED
(
[IDRow] ASC,
[Revision] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
It consists of a composite primary key which is meant to be shown to the user as a single column:
CAST(IDRow AS VARCHAR(4)) + '-' + CAST(Revision AS VARCHAR(3)) AS ItemNumber
So now I want to perhaps add some SQL to the Table_SelectAll Procedure that it only selects the row with the greatest revision number. There are and will be more rows like 1-0, 1-1, 1-2, 1-3...and I want to show only the 1-3 row.
Is it better to solve it inside the SELECT query or maybe in the code-behind while filling the gridControl? Either ways, please help to do this efficiently.
Using SQL Server 2008 R2, VS 2012 and coding in VB.