6 Replies Latest reply: Feb 20, 2018 4:57 PM by William Christensen RSS

    load the first 4 records of a unique key group

    Jim King

      Is there a way to load only the first 4 records of a group in the script .

      Example :  I have a SQL table with multiple customers, but would like to load a maximum of 4 records per customer based on date.

        • Re: load the first 4 records of a unique key group
          Tomasz Truszkowski

          If it's SQL it's better to do it on the database side. Depends on engine: LIMIT with subquery or QUALIFY with OVER.

          Tomasz

          • Re: load the first 4 records of a unique key group
            William Christensen

            BEGIN /** set up config table to reduce the large where clause **/

            DECLARE @TblVar TABLE (tv_PresidentNumber INT, tv_PresidentName NVARCHAR(50), tv_Century INT)

            INSERT INTO @TblVar

            VALUES

            (1, 'George Washington (1789-1797)', 18),

            (2, 'John Adams (1797-1801)', 18),

            (3, 'Thomas Jefferson (1801-1809)', 19),

            (4, 'James Madison (1809-1817)', 19),

            (5, 'James Monroe (1817-1825)', 19),

            (6, 'John Quincy Adams (1825-1829)', 19),

            (7, 'Andrew Jackson (1829-1837)', 19),

            (8, 'Martin Van Buren (1837-1841)', 19),

            (9, 'William Henry Harrison (1841)', 19),

            (10, 'John Tyler (1841-1845)', 19),

            (11, 'James K  Polk (1845-1849)', 19),

            (12, 'Zachary Taylor (1849-1850)', 19),

            (13, 'Millard Fillmore (1850-1853)', 19),

            (14, 'Franklin Pierce (1853-1857)', 19),

            (15, 'James Buchanan (1857-1861)', 19),

            (16, 'Abraham Lincoln (1861-1865)', 19),

            (17, 'Andrew Johnson (1865-1869)', 19),

            (18, 'Ulysses S  Grant (1869-1877)', 19),

            (19, 'Rutherford B  Hayes (1877-1881)', 19),

            (20, 'James A  Garfield (1881)', 19),

            (21, 'Chester Arthur (1881-1885)', 19),

            (22, 'Grover Cleveland (1885-1889)', 19),

            (23, 'Benjamin Harrison (1889-1893)', 19),

            (24, 'Grover Cleveland (1893-1897)', 19),

            (25, 'William McKinley (1897-1901)', 19),

            (26, 'Theodore Roosevelt (1901-1909)', 20),

            (27, 'William Howard Taft (1909-1913)', 20),

            (28, 'Woodrow Wilson (1913-1921)', 20),

            (29, 'Warren G  Harding (1921-1923)', 20),

            (30, 'Calvin Coolidge (1923-1929)', 20),

            (31, 'Herbert Hoover (1929-1933)', 20),

            (32, 'Franklin D  Roosevelt (1933-1945)', 20),

            (33, 'Harry S  Truman (1945-1953)', 20),

            (34, 'Dwight D  Eisenhower (1953-1961)', 20),

            (35, 'John F  Kennedy (1961-1963)', 20),

            (36, 'Lyndon B  Johnson (1963-1969)', 20),

            (37, 'Richard Nixon (1969-1974)', 20),

            (38, 'Gerald Ford (1974-1977)', 20),

            (39, 'Jimmy Carter (1977-1981)', 20),

            (40, 'Ronald Reagan (1981-1989)', 20),

            (41, 'George Bush (1989-1993)', 20),

            (42, 'Bill Clinton (1993-2001)', 20),

            (43, 'George W  Bush (2001-2009)', 20),

            (44, 'Barack Obama (2009-2017)', 20),

            (45, 'Donald Trump (2017- )', 20)

            END


            Select *

            FROM

            (

            SELECT *, ROW_NUMBER() OVER  (PARTITION BY tv_Century ORDER BY tv_PresidentNumber) as RankByCentury

            FROM @TblVar

            ) as tv_qry

            WHERE RankByCentury <= 4