daily pastebin goal
8%
SHARE
TWEET

Untitled

a guest Jan 20th, 2019 71 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
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top