Guest User

Untitled

a guest
Jan 20th, 2019
72
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT Category,
  2. MAX(LastUpdateDateTime) as LastUpdateDateTime
  3. INTO #t
  4. FROM Settings
  5. WHERE CatalogID = 123
  6. GROUP BY Category
  7. ORDER BY Category
  8.  
  9. SELECT s.*
  10. FROM Settings s
  11. INNER JOIN #t t
  12. ON s.Category = t.Category
  13. AND s.LastUpdateDateTime = t.LastUpdateDateTime
  14.  
  15. CREATE TABLE [dbo].[Settings](
  16. [ID] [int] IDENTITY(1,1) NOT NULL,
  17. [CatalogID] [int] NOT NULL,
  18. [Category] [varchar](50) NOT NULL,
  19. [Facings] [bit] NOT NULL,
  20. [Quantity] [bit] NOT NULL,
  21. [LastUpdateDateTime] [datetime] NOT NULL,
  22. CONSTRAINT [PK_Settings_1] PRIMARY KEY CLUSTERED
  23. (
  24. [ID] ASC,
  25. [CatalogID] ASC,
  26. [Category] ASC
  27. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  28. ) ON [PRIMARY]
  29.  
  30. select s1.*
  31. from settings s1
  32. inner join
  33. (
  34. select category, max(LastUpdateDateTime) as LastUpdateDateTime
  35. from settings
  36. where catalogid = 123
  37. group by category
  38. ) s2
  39. on s1.category = s2.category
  40. and s1.LastUpdateDateTime = s2.LastUpdateDateTime
  41.  
  42. ;with cte as
  43. (
  44. select *,
  45. row_number() over(partition by catalogid
  46. order by LastUpdateDateTime desc) rn
  47. from settings
  48. where catalogid = 123
  49. )
  50. select *
  51. from cte
  52. where rn = 1
RAW Paste Data