Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2003
    Posts
    22

    Unanswered: Getting first and last alike records

    I am retrieving data from a table and have it in the format as follows:
    Code:
    SlabNum          MoldWidth          Section
    -----------------------------------------
    1                    4.5                    100
    1                    4.3                    101
    1                    4.2                    102
    1                    4.1                    103
    2                    2.1                    104
    2                    2.5                    105
    2                    2.7                    106
    2                    3.0                    107
    3                    1.1                    108
    3                    1.1                    109
    What I would like to end up with is a table with each row containing just the slab number and the first and last widths. Example:

    Code:
    SlabNumber         BeginningWidth        EndingWidth
    ---------------------------------------------------
    1                       4.5                        4.1
    2                       2.1                        3.0
    3                       1.1                        1.1
    Any suggestions on ways to do this?

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    most likely you want to use a max and a min with a group by.

    as for the details I am not sure. Is the begining width the one with lowest section number?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Sep 2003
    Posts
    22
    I thought about MIN and MAX but the first and last aren't always the min and max values... I need the actual first record of a slab and the last record of a slab.

    Randy

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by RandyRiegel
    I thought about MIN and MAX but the first and last aren't always the min and max values... I need the actual first record of a slab and the last record of a slab.

    Randy

    and how do you define first and last?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Sep 2003
    Posts
    22
    Ok, just talked to co-worker about this.... I only need the FIRST value of each slab... not the last. Like:

    Code:
    SlabNumber         BeginningWidth        -------------------------------------------------
    1                       4.5                        
    2                       2.1                        
    3                       1.1
    Is there an easy way to do this? The records are in order by a field called creation_time. So I'm thinking I can do something with MIN(Creation_Time) to get the first one of each group.

    Randy
    Last edited by RandyRiegel; 10-12-07 at 16:06.

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    that is correct
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Sep 2003
    Posts
    22
    Code:
    select P.productid, P.SlabNumber, S.topmoldwidth, S.CreationTime
          from product P
          join section S on P.ProductId = S.ProductId 
          where P.CreationTime > '10/11/2007' and P.SlabNumber is not null
    	and P.SlabNumber <> 'Crop'
          order by P.ProductId, S.CreationTime
    How do I change the above to only choose the smallest time from each SlabNum. I put MIN() around creation time and it makes me add all the other fiields to the group clause and i still get more than one line for each slab. I don't do a lot of groupings in SQL... most of my query's are pretty simple.

    Randy

  8. #8
    Join Date
    Sep 2003
    Posts
    22
    sorry, accidently double posted last message

  9. #9
    Join Date
    Apr 2007
    Posts
    183
    Code:
    DECLARE	@Sample TABLE (SlabNum INT, MoldWidth DECIMAL(3, 1), Section INT)
    
    INSERT	@Sample
    SELECT	1, 4.5, 100 UNION ALL
    SELECT	1, 4.3, 101 UNION ALL
    SELECT	1, 4.2, 102 UNION ALL
    SELECT	1, 4.1, 103 UNION ALL
    SELECT	2, 2.1, 104 UNION ALL
    SELECT	2, 2.5, 105 UNION ALL
    SELECT	2, 2.7, 106 UNION ALL
    SELECT	2, 3.0, 107 UNION ALL
    SELECT	3, 1.1, 108 UNION ALL
    SELECT	3, 1.1, 109
    
    -- SQL Server 2000
    SELECT DISTINCT	s1.SlabNum,
    		(SELECT TOP 1 s2.MoldWidth FROM @Sample AS s2 WHERE s2.SlabNum = s1.SlabNum ORDER BY s2.Section) AS MoldWidth
    FROM		@Sample AS s1
    ORDER BY	s1.SlabNum
    
    -- SQL Server 2005
    SELECT		SlabNum,
    		MoldWidth
    FROM		(
    			SELECT	SlabNum,
    				MoldWidth,
    				ROW_NUMBER() OVER (PARTITION BY SlabNum ORDER BY Section) AS RecID
    			FROM	@Sample
    		) AS d
    WHERE		RecID = 1
    ORDER BY	SlabNum

Posting Permissions

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