Results 1 to 8 of 8
  1. #1
    Join Date
    May 2014
    Posts
    16

    Red face Unanswered: TOP 5 values in SQL Statement

    Hi, i have the below data with two columns (Posting Date and Pond Crop, can you please help me construct an SQL where i will pick and display only the TOP 3 Posting date of EACH Pond Crop. I only attached less data but in reality there are lots of Ponds and posting date in this table.
    Table Name : Weekly Harvest
    Fields:
    1. Posting Date
    2. PondCrop

    Desired Output:
    Posting Date PondCrop
    2011-12-12 00:00:00.000 01PA01-15
    2011-12-19 00:00:00.000 01PA01-15
    2011-12-26 00:00:00.000 01PA01-15
    2012-03-19 00:00:00.000 01PA01-16
    2012-03-20 00:00:00.000 01PA01-16
    2012-03-26 00:00:00.000 01PA01-16

    Raw Data
    Posting Date PondCrop
    2011-11-21 00:00:00.000 01PA01-15
    2011-11-28 00:00:00.000 01PA01-15
    2011-12-02 00:00:00.000 01PA01-15
    2011-12-05 00:00:00.000 01PA01-15
    2011-12-12 00:00:00.000 01PA01-15
    2011-12-19 00:00:00.000 01PA01-15
    2011-12-26 00:00:00.000 01PA01-15
    2012-01-30 00:00:00.000 01PA01-16
    2012-01-31 00:00:00.000 01PA01-16
    2012-02-06 00:00:00.000 01PA01-16
    2012-02-13 00:00:00.000 01PA01-16
    2012-02-20 00:00:00.000 01PA01-16
    2012-02-27 00:00:00.000 01PA01-16
    2012-03-06 00:00:00.000 01PA01-16
    2012-03-12 00:00:00.000 01PA01-16
    2012-03-19 00:00:00.000 01PA01-16
    2012-03-20 00:00:00.000 01PA01-16
    2012-03-26 00:00:00.000 01PA01-16

  2. #2
    Join Date
    May 2014
    Posts
    16

    Unhappy How to get top 3 data for each group columns

    Hi i want to get the top 3 Posting Date for each PondCrop in the below sample data using SELECT statement/Group query.

    Posting date PondCrop
    01/01/2014 Pond1Crop1
    02/01/2013 Pond1Crop1
    01/02/2014 Pond1Crop1
    11/03/2014 Pond1Crop1
    02/01/2014 Pond1Crop2
    02/02/2014 Pond1Crop2
    01/03/2014 Pond1Crop2
    11/04/2014 Pond1Crop2

    output:
    01/01/2014 Pond1Crop1
    01/02/2014 Pond1Crop1
    11/03/2014 Pond1Crop1
    02/02/2014 Pond1Crop2
    01/03/2014 Pond1Crop2
    11/04/2014 Pond1Crop2

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What was the format of Posting date?
    Was it dd/mm/yyyy?

  4. #4
    Join Date
    May 2014
    Posts
    16
    Quote Originally Posted by tonkuma View Post
    What was the format of Posting date?
    Was it dd/mm/yyyy?
    yes dd/mm/yyyy

  5. #5
    Join Date
    May 2014
    Posts
    16

    TOP 5 values in SQL Statement

    yes dd/mm/yyyy

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please consider ROW_NUMBER function.

    ROW_NUMBER (Transact-SQL)

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Here's a working NZDF solution to help you on your way!
    Code:
    DECLARE @t TABLE (
       [Posting Date]   DATETIME        NOT NULL
    ,  PondCrop         VARCHAR(20)     NOT NULL
    )
    
    INSERT INTO @t ([Posting Date], [PondCrop]) VALUES
       ('2011-11-21', '01PA01-15'),  ('2011-11-28', '01PA01-15')
    ,  ('2011-12-02', '01PA01-15'),  ('2011-12-05', '01PA01-15')
    ,  ('2011-12-12', '01PA01-15'),  ('2011-12-19', '01PA01-15')
    ,  ('2011-12-26', '01PA01-15'),  ('2012-01-30', '01PA01-16')
    ,  ('2012-01-31', '01PA01-16'),  ('2012-02-06', '01PA01-16')
    ,  ('2012-02-13', '01PA01-16'),  ('2012-02-20', '01PA01-16')
    ,  ('2012-02-27', '01PA01-16'),  ('2012-03-06', '01PA01-16')
    ,  ('2012-03-12', '01PA01-16'),  ('2012-03-19', '01PA01-16')
    ,  ('2012-03-20', '01PA01-16'),  ('2012-03-26', '01PA01-16')
    
    ; WITH cte AS (
    SELECT *
    ,  ROW_NUMBER() OVER (PARTITION BY [PondCrop] ORDER BY [Posting Date]) AS cte
       FROM @t
    ) SELECT [Posting Date], [PondCrop]
       FROM cte
       WHERE  cte <= 3
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    May 2014
    Posts
    16

    top value

    Quote Originally Posted by Pat Phelan View Post
    Here's a working NZDF solution to help you on your way!
    Code:
    DECLARE @t TABLE (
       [Posting Date]   DATETIME        NOT NULL
    ,  PondCrop         VARCHAR(20)     NOT NULL
    )
    
    INSERT INTO @t ([Posting Date], [PondCrop]) VALUES
       ('2011-11-21', '01PA01-15'),  ('2011-11-28', '01PA01-15')
    ,  ('2011-12-02', '01PA01-15'),  ('2011-12-05', '01PA01-15')
    ,  ('2011-12-12', '01PA01-15'),  ('2011-12-19', '01PA01-15')
    ,  ('2011-12-26', '01PA01-15'),  ('2012-01-30', '01PA01-16')
    ,  ('2012-01-31', '01PA01-16'),  ('2012-02-06', '01PA01-16')
    ,  ('2012-02-13', '01PA01-16'),  ('2012-02-20', '01PA01-16')
    ,  ('2012-02-27', '01PA01-16'),  ('2012-03-06', '01PA01-16')
    ,  ('2012-03-12', '01PA01-16'),  ('2012-03-19', '01PA01-16')
    ,  ('2012-03-20', '01PA01-16'),  ('2012-03-26', '01PA01-16')
    
    ; WITH cte AS (
    SELECT *
    ,  ROW_NUMBER() OVER (PARTITION BY [PondCrop] ORDER BY [Posting Date]) AS cte
       FROM @t
    ) SELECT [Posting Date], [PondCrop]
       FROM cte
       WHERE  cte <= 3
    -PatP


    Thank you i got it, i just made slight adjustment with the values and additional field ABW, data comes from the table so i made like this and it works.

    DECLARE @t TABLE ([PostingDate] DATETIME NOT NULL, PondCrop VARCHAR(20) NOT NULL, ABW REAL)

    INSERT INTO @t ([PostingDate], [PondCrop],ABW)
    SELECT [PostingDate], [PondCrop], ABW FROM [intelliviewdb].[dbo].[GO_Weekly_Report]
    ; WITH cte AS (
    SELECT *
    , ROW_NUMBER() OVER (PARTITION BY [PondCrop] ORDER BY [PostingDate] DESC) AS cte
    FROM @t
    ) SELECT [PostingDate], [PondCrop],ABW
    FROM cte
    WHERE cte <= 4

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •