Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT Category,
- MAX(LastUpdateDateTime) as LastUpdateDateTime
- INTO #t
- FROM Settings
- WHERE CatalogID = 123
- GROUP BY Category
- ORDER BY Category
- SELECT s.*
- FROM Settings s
- INNER JOIN #t t
- ON s.Category = t.Category
- AND s.LastUpdateDateTime = t.LastUpdateDateTime
- CREATE TABLE [dbo].[Settings](
- [ID] [int] IDENTITY(1,1) NOT NULL,
- [CatalogID] [int] NOT NULL,
- [Category] [varchar](50) NOT NULL,
- [Facings] [bit] NOT NULL,
- [Quantity] [bit] NOT NULL,
- [LastUpdateDateTime] [datetime] NOT NULL,
- CONSTRAINT [PK_Settings_1] PRIMARY KEY CLUSTERED
- (
- [ID] ASC,
- [CatalogID] ASC,
- [Category] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
- select s1.*
- from settings s1
- inner join
- (
- select category, max(LastUpdateDateTime) as LastUpdateDateTime
- from settings
- where catalogid = 123
- group by category
- ) s2
- on s1.category = s2.category
- and s1.LastUpdateDateTime = s2.LastUpdateDateTime
- ;with cte as
- (
- select *,
- row_number() over(partition by catalogid
- order by LastUpdateDateTime desc) rn
- from settings
- where catalogid = 123
- )
- select *
- from cte
- where rn = 1
Add Comment
Please, Sign In to add comment