Results 1 to 13 of 13
  1. #1
    Join Date
    Nov 2005
    Posts
    91

    Unanswered: SQL Query Help - Group By and Min Function

    Can someone please help me formulate a query to return the first instance (ex 1234, 33, 9/28/ 2009 1:10:33 PM) from the example source data below.

    column1 column2 column3
    1234 35.1 9/30/2009 1:10:19 AM
    1234 33 9/29/2009 1:10:33 AM
    1234 35.1 10/1/2009 1:10:19 AM
    1234 33 9/28/2009 1:10:33 PM
    .
    .
    .


    The query below that I've created is not returning only the 1 row that I am expecting.

    select column1, column2, min(column3)
    from table
    group by column1, column2

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
    WITH CTE AS
    (SELECT column1, 
         column2, 
         TimeColumn,
         ROW_NUMBER() (PARTITION OVER column1, column2 ORDER BY TimeColumn ASC) as RowNum
    FROM MyTable)
    SELECT column1, 
         column2, 
         TimeColumn
    FROM CTE
    WHERE RowNum = 1
    I don't have SQL Server at hand, the code might contain typo's. This code works only on SQL Server 2005 and up.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Nov 2005
    Posts
    91
    Thanks for the reply Wim, sorry I should have mentioned the version of SQL that I am using. Unfortunately I'm working with SQL Server 2000 vs 2005. Is this still a possibility? I tried getting around this issue by connecting to my SQL 2000 database via SQL Server 2005 to apply your logic but had no luck. Are there any other options? Thanks.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT column1
         , column2
         , column3
      FROM ( SELECT MIN(column3) AS first_instance
               FROM daTable ) AS m
    INNER
      JOIN daTable 
        ON daTable.column3 = m.first_instance
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2005
    Posts
    91
    Hi r937. I tried your solution but unfortunately it doesn't work for me because finding the very first instance of a date in my table will not solve my problem when there are multiple groups in column 1. See example below. From the source data I am looking to extract the row that corresponds to the first date instance per group in column1 (ex 1234, 5678). There are many more groups in column 1, I just use 2 as an example. Hope this makes sense?

    -1234, 33, 9/28/ 2009 1:10:33 PM
    -5678, 33.7, 4/1/2008 4:45 PM


    Source Data Example

    column1 column2 column3
    1234 35.1 9/30/2009 1:10:19 AM
    1234 33 9/29/2009 1:10:33 AM
    1234 35.1 10/1/2009 1:10:19 AM
    1234 33 9/28/2009 1:10:33 PM
    5678 30 5/1/2009 5:50:11 AM
    5678 33.7 4/1/2008 4:45 PM

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    SELECT column1, MIN(column2), MIN(column3)
    FROM table
    WHERE column1 = 1234
    GROUP BY column1
    Seems to have some pieces missing

    Why don't you supply some more sample data than you have here...for examples of different col1s
    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.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dsmbwoy
    extract the row that corresponds to the first date instance per group in column1
    this is ~not~ what you asked the first time

    you said you wanted exactly 1 row as the result, and that's what i gave you

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Nov 2005
    Posts
    91
    You're absolutely right r937, my mistake for not supplying enough details.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no problem, i just wanted to clarify that my solution was actually what you asked for

    try this now --
    Code:
    SELECT column1
         , column2
         , column3
      FROM ( SELECT column1
                  , MIN(column3) AS first_instance
               FROM daTable
             GROUP
                 BY column1 ) AS m
    INNER
      JOIN daTable 
        ON daTable.column1 = m.column1
       AND daTable.column3 = m.first_instance
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Nov 2005
    Posts
    91
    Hi Brett Kaiser, the problem that I would have with using your solution is that I only need the min value of column3 per group in column1. The data from column2 would be whatever value column2 would have for that particular min row in column3 for the group in column1. In my example below I am looking to achieve the following results.

    Desired Result
    Column1....Column2.......Column3
    -----------------------------------
    1..............10..............01/01/2008
    2..............44..............01/07/2008
    3..............23..............08/01/2009
    4..............45..............06/05/2009


    Sample Table Data
    ------------------
    ColumnA.....ColumnB.....ColumnC
    --------------------------------
    1..............10..............01/01/2008
    1..............33..............01/01/2009
    1..............9.5.............05/05/2009
    2..............11..............03/01/2008
    2..............50..............05/04/2009
    2..............44..............01/07/2008
    3..............23..............08/01/2009
    3..............78..............12/01/2009
    4..............45..............06/05/2009
    4.............78...............12/07/2009

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Rudy is doing the same..but..you can cut and paste this to see it work

    Code:
    SET NOCOUNT ON
    GO
    
    CREATE TABLE #myTable99(ColumnA int, ColumnB int, ColumnC datetime)
    GO
    
    INSERT INTO #myTable99
    	(ColumnA, ColumnB, ColumnC)
    SELECT 1,10, '01/01/2008' UNION ALL
    SELECT 1,33, '01/01/2009' UNION ALL
    SELECT 1,95, '05/05/2009' UNION ALL
    SELECT 2,11, '03/01/2008' UNION ALL
    SELECT 2,50, '05/04/2009' UNION ALL
    SELECT 2,44, '01/07/2008' UNION ALL
    SELECT 3,23, '08/01/2009' UNION ALL
    SELECT 3,78, '12/01/2009' UNION ALL
    SELECT 4,45, '06/05/2009' UNION ALL
    SELECT 4,78, '12/07/2009'
    GO
    
    SELECT *
      FROM #myTable99 o
    WHERE EXISTS ( SELECT i.ColumnA
    				 FROM #myTable99 i
    			    WHERE i.ColumnA = o.ColumnA
    			 GROUP BY i.ColumnA
    			   HAVING MIN(i.ColumnC) = o.ColumnC)
    GO
    
    DROP TABLE #myTable99
    GO
    
    /*
    
    Returns
    
    ColumnA     ColumnB     ColumnC
    ----------- ----------- -----------------------
    1           10          2008-01-01 00:00:00.000
    2           44          2008-01-07 00:00:00.000
    3           23          2009-08-01 00:00:00.000
    4           45          2009-06-05 00:00:00.000
    
    */
    You can also use [ code] [ /code] tags (w/out the space) to post here to retain formatting
    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.

  12. #12
    Join Date
    Nov 2005
    Posts
    91
    Thanks for all the help guys!!!

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Sumit Kumar
    Please write below query, i hope it help you.

    select column1, column2, min(column3)
    from table
    group by column1, column2 , column3
    having column3=(select min(column3) from table)

    Database Design ? Sagar Information Technologies


    ahhhhh...no
    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.

Posting Permissions

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