I am retrieving data from a table and have it in the format as follows:
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:
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?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?|||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|||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?|||Ok, just talked to co-worker about this... I only need the FIRST value of each slab... not the last. Like:
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|||that is correct|||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|||sorry, accidently double posted last message|||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
