Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2012
    Posts
    16

    Unanswered: Lowest Year & Month

    I am attempting to only query the lowest year, and month from my entire production database. I am using the following query and when I do it individually it will bring back the lowest year but also with additional years. How can I remove those other years so I can only query the lowest year and month from the entire database?

    Code:
    select distinct RRCID, MIN(YEAR)as YEAR, MONTH
    from Gas_Prodv1
    where RRCID = 1221376
    group by RRCID, MONTH
    order by YEAR, MONTH

    Results:
    1221376 2007 1 <----- Only want this record
    1221376 2007 2
    1221376 2007 3
    1221376 2007 5
    1221376 2007 6
    1221376 2007 7
    1221376 2007 8
    1221376 2007 9
    1221376 2007 10
    1221376 2007 11
    1221376 2007 12

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How's about

    SELECT * FROM Gas_Prodv1 o WHERE EXISTS (
    SELECT * FROM Gas_Prodv1 i
    WHERE o.[YEAR] = MIN(i.[YEAR]) AND o.[MONTH] = MIN(i.[MONTH])
    )
    AND RRCID = 1221376
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT TOP 1 RRCID, YEAR, MONTH
       FROM Gas_Prodv1
       WHERE RRCID = 1221376
       ORDER BY YEAR, MONTH
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Pat's will be more efficient....damn hangover
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jan 2012
    Posts
    16
    Can you use Pats though to query the entire database though and give an accurate date for all RRCIDs?

    I tried to use Brett's but I received an error message:
    An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by tech_wiz2008 View Post
    Can you use Pats though to query the entire database though and give an accurate date for all RRCIDs?
    Ah, that's a different question than the first one that you asked.
    Code:
    SELECT RRCD, YEAR, MONTH
       FROM (SELECT RRCD, YEAR, MONTH
    ,     Row_Count(*) OVER (
             PARTITION RRCD
             ORDER BY YEAR, MONTH) AS rc
          FROM Gas_Prodv1) AS z
       WHERE  1 = z.rc
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Row_count()?

    Don't you mean Row_Number()?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Good point, I didn't sin-tax check the air-code.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK, try this then

    SELECT * FROM Gas_Prodv1 o WHERE EXISTS (
    SELECT * FROM Gas_Prodv1 i
    GROUP BY RRCD
    HAVING o.[YEAR] = MIN(i.[YEAR]) AND o.[MONTH] = MIN(i.[MONTH])
    )
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Jan 2012
    Posts
    16
    Unfortunately none of these have worked so far. I've gotten close to the solution, by eliminating the duplicate RRCIDS (lease numbers) and finding the lowest year but I am still having trouble finding the lowest month associated with the lowest year. Any ideas?

    Code:
    SELECT distinct RRCID, MIN(DISTINCT YEAR) AS Year, MONTH
    FROM Gas_Prodv1
    Group by RRCID, MONTH
    ORDER BY YEAR ASC

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What is wrong with the output from:
    Code:
    SELECT RRCD, YEAR, MONTH
       FROM (SELECT RRCD, YEAR, MONTH
    ,     Row_Number(*) OVER (
             PARTITION RRCD
             ORDER BY YEAR, MONTH) AS rc
          FROM Gas_Prodv1) AS z
       WHERE  1 = z.rc
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  12. #12
    Join Date
    Jan 2012
    Posts
    16
    Pat,

    It didn't give me an output, I received the following error from it:

    Msg 102, Level 15, State 1, Line 3
    Incorrect syntax near 'RRCID'.

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ugh, that's what I get for failing to properly check the sin-tax. This actually works:
    Code:
    CREATE TABLE #foo (
       RRCD     INT NOT NULL
    ,  [YEAR]   INT NOT NULL
    ,  [MONTH]  INT NOT NULL
    )
    
    INSERT INTO #foo (RRCD, YEAR, MONTH)
       VALUES 
       (1221373, 2007,  1), (1221373, 2007,  2), (1221373, 2007,  3)
    ,  (1221373, 2007,  4), (1221373, 2007,  5), (1221373, 2007,  6)
    ,  (1221373, 2007,  7), (1221373, 2007,  8), (1221373, 2007,  9)
    ,  (1221373, 2007, 10), (1221373, 2007, 11), (1221373, 2007, 12)
    
    ,  (1221376, 2007,  1), (1221376, 2007,  2), (1221376, 2007,  3)
    ,  (1221376, 2007,  4), (1221376, 2007,  5), (1221376, 2007,  6)
    ,  (1221376, 2007,  7), (1221376, 2007,  8), (1221376, 2007,  9)
    ,  (1221376, 2007, 10), (1221376, 2007, 11), (1221376, 2007, 12)
    
    SELECT RRCD, YEAR, MONTH
       FROM (SELECT RRCD, YEAR, MONTH
    ,     Row_Number() OVER (
             PARTITION BY RRCD
             ORDER BY YEAR, MONTH) AS rc
          FROM #foo) AS z
       WHERE  1 = z.rc
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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