April 2, 2002 at 10:16 am
I have a table containing multiple rows for each name. Each name contains from 1 to 500 rows (or transactions) in a recordset. How can I return the first 3 records of each recordset group by name using T-SQL?
April 2, 2002 at 4:07 pm
Sounds like maybe a simple group by with a having count(*)<=3 would do it. If you post some sample data and DDL we can help you a little better.
Andy
April 3, 2002 at 8:39 am
I have done this on numerous occassions and will be glad to help. Please post the DDL of the table as this will help me better understand your needs.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 3, 2002 at 9:21 am
Here is some sample data. The records with the "*" at the end are the ones I want the query to return. (ie. the first 3 records, if available for each UWI). Also included is the DDL.
Is there a way to attach files rather than paste data?
if exists (select * from sysobjects where id = object_id(N'[dbo].[wellprod]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[wellprod]
GO
CREATE TABLE [dbo].[wellprod] (
[uwi] [varchar] (21) NOT NULL ,
[record] [varchar] (4) NULL ,
[month] [int] NOT NULL ,
[year] [int] NOT NULL ,
[oil_mo] [decimal](18, 1) NULL ,
[gas_mo] [decimal](18, 1) NULL ,
[water_mo] [decimal](18, 1) NULL ,
[inj_mo] [decimal](18, 1) NULL ,
[hours] [decimal](18, 1) NULL ,
[units] [varchar] (1) NULL
) ON [PRIMARY]
GO
uwi record month year oil_mo gas_mo water_mo inj_mo hours units
200A064E082J0700 0250 8 1992 .0 .0 11.6 .0 53.0 1*
200A064E082J0700 0250 9 1992 .0 120.7 264.0 .0 384.0 1*
200A064E082J0700 0250 10 1992 .0 688.6 91.4 .0 336.0 1*
200D008J093I1402 0250 8 1996 .0 38400.7 650.0 .0 326.0 1*
200D008J093I1400 0250 10 1994 .0 37630.5 1.6 .0 24.0 1*
200D008J093I1400 0250 11 1994 .0 440026.4 38.0 .0 670.0 1*
200D008J093I1400 0250 12 1994 .0 410222.3 105.4 .0 701.0 1*
200D008J093I1400 0250 1 1995 .0 343998.1 165.7 .0 696.0 1
200D008J093I1400 0250 2 1995 .0 291400.0 180.6 .0 670.0 1
200D008J093I1400 0250 3 1995 .0 263523.2 185.7 .0 708.0 1
200D008J093I1400 0250 4 1995 .0 209185.8 169.0 .0 718.0 1
200D008J093I1400 0250 5 1995 .0 184794.6 217.5 .0 727.0 1
200D008J093I1400 0250 6 1995 .0 130425.3 161.0 .0 667.0 1
200D008J093I1400 0250 7 1995 .0 36001.3 47.5 .0 230.0 1
200D008J093I1400 0250 8 1995 .0 197962.7 916.5 .0 511.0 1
200D008J093I1400 0250 9 1995 .0 159196.5 644.7 .0 590.0 1
200D008J093I1400 0250 10 1995 .0 184475.1 681.8 .0 732.0 1
200D008J093I1400 0250 11 1995 .0 119507.4 670.0 .0 710.0 1
200D008J093I1400 0250 12 1995 .0 102229.0 631.4 .0 674.0 1
200D008J093I1400 0250 1 1996 .0 86242.7 674.0 .0 629.0 1
200D008J093I1400 0250 2 1996 .0 71104.6 581.0 .0 540.0 1
200D008J093I1400 0250 3 1996 .0 63423.7 606.0 .0 686.0 1
200D008J093I1400 0250 4 1996 .0 53265.4 636.0 .0 588.0 1
200D008J093I1400 0250 5 1996 .0 .0 1.0 .0 .0 1
200C094D093P0302 0250 7 2000 .0 232892.1 344.8 .0 288.0 1*
200C094D093P0302 0250 8 2000 .0 595371.8 195.4 .0 610.0 1*
200C094D093P0302 0250 9 2000 .0 472173.1 213.9 .0 571.0 1*
200C094D093P0302 0250 10 2000 .0 527170.6 421.7 .0 742.0 1
200C094D093P0302 0250 11 2000 .0 378895.6 292.7 .0 703.0 1
200C094D093P0302 0250 12 2000 .0 324625.7 189.1 .0 732.0 1
200C094D093P0302 0250 1 2001 .0 273468.5 220.8 .0 730.0 1
200C094D093P0302 0250 2 2001 .0 210776.0 260.6 .0 655.0 1
200C094D093P0302 0250 3 2001 .0 204834.3 247.0 .0 725.0 1
200C094D093P0302 0250 4 2001 .0 175268.0 198.9 .0 713.0 1
200C094D093P0302 0250 5 2001 .0 144956.4 166.1 .0 614.0 1
200C094D093P0302 0250 6 2001 .0 142862.3 134.6 .0 554.0 1
200C094D093P0302 0250 7 2001 .0 146873.1 117.0 .0 730.0 1
200C094D093P0302 0250 8 2001 .0 135302.1 119.0 .0 708.0 1
200C094D093P0302 0250 10 2001 .0 122488.9 107.5 .0 715.0 1
200C094D093P0302 0250 11 2001 .0 112337.7 107.2 .0 708.0 1
200A077D093P0300 0250 5 1995 .0 9253.2 46.1 .0 19.0 1*
200A077D093P0300 0250 8 1996 .0 293983.9 37.7 .0 250.0 1*
200A077D093P0300 0250 9 1996 .0 732973.9 71.9 .0 598.0 1*
200A077D093P0300 0250 10 1996 .0 703414.7 71.2 .0 677.0 1
200A077D093P0300 0250 11 1996 .0 571953.1 75.3 .0 708.0 1
200A077D093P0300 0250 12 1996 .0 387158.5 45.1 .0 571.0 1
200A077D093P0300 0250 1 1997 .0 403808.6 54.1 .0 722.0 1
200A077D093P0300 0250 2 1997 .0 357120.2 14.1 .0 670.0 1
200A077D093P0300 0250 3 1997 .0 314052.1 20.9 .0 737.0 1
200A077D093P0300 0250 4 1997 .0 225261.0 15.2 .0 713.0 1
200A077D093P0300 0250 5 1997 .0 240555.2 16.6 .0 744.0 1
200A077D093P0300 0250 6 1997 .0 245929.0 19.5 .0 713.0 1
200A077D093P0300 0250 7 1997 .0 270103.7 21.8 .0 730.0 1
200A077D093P0300 0250 8 1997 .0 306740.4 29.2 .0 598.0 1
200A077D093P0300 0250 9 1997 .0 43728.3 4.0 .0 103.0 1
200A077D093P0300 0250 10 1997 .0 313977.5 27.2 .0 619.0 1
200A077D093P0300 0250 11 1997 .0 355934.7 30.4 .0 689.0 1
200A077D093P0300 0250 12 1997 .0 561439.8 70.3 .0 734.0 1
200A077D093P0300 0250 1 1998 .0 555909.9 99.5 .0 742.0 1
200A077D093P0300 0250 2 1998 .0 420451.6 90.6 .0 662.0 1
200A077D093P0300 0250 3 1998 .0 409306.6 110.1 .0 744.0 1
200A077D093P0300 0250 4 1998 .0 348995.6 126.5 .0 720.0 1
200A077D093P0300 0250 5 1998 .0 310666.0 385.4 .0 734.0 1
200A077D093P0300 0250 6 1998 .0 253148.4 600.8 .0 646.0 1
200A077D093P0300 0250 7 1998 .0 233555.8 537.5 .0 617.0 1
200A077D093P0300 0250 8 1998 .0 227035.6 648.6 .0 727.0 1
200A077D093P0300 0250 9 1998 .0 196493.3 620.6 .0 706.0 1
200A077D093P0300 0250 10 1998 .0 181252.3 642.6 .0 739.0 1
200A077D093P0300 0250 11 1998 .0 162461.9 619.9 .0 715.0 1
200A077D093P0300 0250 12 1998 .0 157013.6 643.1 .0 739.0 1
200A077D093P0300 0250 1 1999 .0 149027.5 629.0 .0 725.0 1
200A077D093P0300 0250 2 1999 .0 131451.0 568.9 .0 655.0 1
200A077D093P0300 0250 3 1999 .0 131752.7 570.3 .0 665.0 1
200A077D093P0300 0250 4 1999 .0 147547.4 520.1 .0 564.0 1
200A077D093P0300 0250 5 1999 .0 128735.8 620.3 .0 725.0 1
200A077D093P0300 0250 6 1999 .0 111201.9 612.5 .0 718.0 1
200A077D093P0300 0250 7 1999 .0 101192.6 581.7 .0 686.0 1
200A077D093P0300 0250 8 1999 .0 83559.3 593.7 .0 708.0 1
200A077D093P0300 0250 11 1999 .0 91463.8 295.7 .0 346.0 1
200A077D093P0300 0250 12 1999 .0 175037.3 669.0 .0 739.0 1
200A077D093P0300 0250 1 2000 .0 131397.8 649.7 .0 727.0 1
200A077D093P0300 0250 2 2000 .0 118797.5 602.5 .0 672.0 1
200A077D093P0300 0250 3 2000 .0 124217.4 631.1 .0 696.0 1
200A077D093P0300 0250 4 2000 .0 123166.8 403.5 .0 398.0 1
200A077D093P0300 0250 5 2000 .0 24881.1 124.6 .0 144.0 1
200A077D093P0300 0250 6 2000 .0 110665.9 452.5 .0 518.0 1
200A077D093P0300 0250 7 2000 .0 75438.4 343.6 .0 384.0 1
200A077D093P0300 0250 12 2000 .0 52033.8 291.8 .0 343.0 1
200A077D093P0300 0250 1 2001 .0 113253.4 627.4 .0 730.0 1
200A077D093P0300 0250 2 2001 .0 94534.0 571.1 .0 655.0 1
200A077D093P0300 0250 3 2001 .0 82593.9 623.5 .0 722.0 1
200A077D093P0300 0250 4 2001 .0 75459.7 616.2 .0 718.0 1
200A077D093P0300 0250 5 2001 .0 62610.9 527.9 .0 614.0 1
200A077D093P0300 0250 6 2001 .0 49992.9 467.2 .0 550.0 1
200A077D093P0300 0250 7 2001 .0 47561.6 616.5 .0 730.0 1
200A077D093P0300 0250 8 2001 .0 46141.8 596.1 .0 706.0 1
200A077D093P0300 0250 9 2001 .0 43195.9 560.0 .0 665.0 1
200A077D093P0300 0250 10 2001 .0 42308.5 549.5 .0 650.0 1
200A077D093P0300 0250 11 2001 .0 46120.6 599.3 .0 708.0 1
April 3, 2002 at 11:35 am
This query will do the trick.
Thanks all for your help.
SELECT *
FROM wellprod t1
WHERE EXISTS
(
SELECT 1
FROM
(
SELECT TOP 3 year, gas_mo
FROM wellprod t3
WHERE t1.uwi = t3.uwi
ORDER BY t1.uwi, year, month
) AS t2
WHERE t2.year = t1.year
AND t2.gas_mo = t1.gas_mo
)
April 11, 2002 at 4:14 am
SELECT *
FROM wellprod wellprod1
WHERE EXISTS
(SELECT 1
FROM
(SELECT year, gas_mo
FROM wellprod wellprod3
WHERE wellprod1.uwi= wellprod3.uwi
Group By year, gas_mo
Having count(*) <=3
) AS wellprod2
WHERE wellprod2.year= wellprod1.year AND wellprod2.gas_mo= wellprod1.gas_mo)
ORDER BY year, gas_mo
Shrinivas L.K.
Shrinivas L.K.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply