Select TOP N in Group - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Select TOP N in Group

Author

Topic

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2008-01-04 : 11:34:08

I have this SQL...

Select Client.Customer_No, Client.City,
WashRun.LF_Desc AS Formula,
SUM(WashRun.LF_WT_Target) AS Target_Weight
FROM Client
INNER JOIN WashRun ON WashRun.Customer_No = Client.Customer_No AND
	WashRun.BOF_Date BETWEEN '10/1/2007' and '01/01/2008'
WHERE Client.Company Like 'DEMO'
GROUP BY Client.Customer_No, Client.City,WashRun.LF_Desc 
ORDER By Client.City, Target_Weight DESC

This returns data like so...

15145 Albuquerque D - Mats 539134
15145 Albuquerque N - Butcher Coats 279475
15145 Albuquerque A - Col Pants / Covs / Cotton 279230
15145 Albuquerque M - Bar / Wind / Glass Towel 217277
15145 Albuquerque N4 - Meat Cutter Gloves 138000
15145 Albuquerque B - Color Shirts 123280
15145 Albuquerque E - Shop Towels 105624
15145 Albuquerque E7 - Dust Mops / CRT / Fender 101725
15145 Albuquerque H1 -Bath Towels - Hospital 47250
15145 Albuquerque B1 - Heavy Soil Garments 40905
15145 Albuquerque B3 - Heavy Soil Garments 38210
15145 Albuquerque Z1 - Blue Bar Towel 36675
15145 Albuquerque J3 - Col Food Service / Apron 33075
15145 Albuquerque N - Butcher Coats / Wht Apron 19685
15145 Albuquerque P - Executive Shirts 13900
15145 Albuquerque C - White Industrial Garments 13500
15145 Albuquerque O - Executive Pants 11000
15145 Albuquerque I - Inkers 7077
15145 Albuquerque E6 - New Dyed Shop Towels 5900
15145 Albuquerque B5 - Yellow Transportation 2000
15145 Albuquerque D - ESD Garments 1800
15145 Albuquerque H1 - Bath Towels - Hospital 675
15145 Albuquerque H1 - Med Soil White Linen 500
15145 Albuquerque B4 - Knit Shirts 368
15145 Albuquerque W - Resour 200
11529 Belleville Mats 658280
11529 Belleville Bar Mops 147440
11529 Belleville Cotton Garments 129240
11529 Belleville Color Pants 115535
11529 Belleville Heavy Soil Shirts 56520
11529 Belleville Schering 55980
11529 Belleville Color Shirts 53280
11529 Belleville Butcher Coats 47070
11529 Belleville Whites 21675
11529 Belleville Heavy Soil Cotton 18075
11529 Belleville Colors 15825
11529 Belleville Bath Towels 14400
11529 Belleville Schering Poly Coats 12150
11529 Belleville Mops 9035
11529 Belleville Sun Chemical 4680
11529 Belleville Yellow Freight 3075
11529 Belleville Kraft Foods 1755
11529 Belleville Clean Machine 1125
11529 Belleville Aprons 675
11529 Belleville New White Towels 450
11529 Belleville 0
12441 Coppell Mats (D) 1626240
12441 Coppell Jeans (A5) 691875
12441 Coppell Colored Pants (A) 606345
12441 Coppell Colored Shirts (B) 349820
12441 Coppell Cotton Shirts (A5) 303425

How can I get this SQL to only return the TOP 3 records in each Client.City?

TIA

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2008-01-04 : 11:36:05

Take a look at http://www.sqlteam.com/article/using-cross-apply-in-sql-server-2005

=================================================
Creating tomorrow's legacy systems today. One crisis at a time.

pootle_flump

1064 Posts

Posted - 2008-01-04 : 11:43:55

Ken - remove the trailing dot from the link

Graz - aren't these SS 2005 specific?

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2008-01-04 : 11:46:16

Ack! Yes, they are SQL Server 2005 specific. My apologies for that. Sorry, I don't have a solution off the top of my head in SQL Server 2000.

=================================================
Creating tomorrow's legacy systems today. One crisis at a time.

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2008-01-04 : 11:49:46

Thanks but we are still using SQL 2000.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-04 : 12:07:39

Try this:-

SELECT t.* FROM
(
      Select Client.Customer_No, Client.City,
      WashRun.LF_Desc AS Formula,
      SUM(WashRun.LF_WT_Target) AS Target_Weight
      FROM Client
      INNER JOIN WashRun 
      ON WashRun.Customer_No = Client.Customer_No 
      AND WashRun.BOF_Date BETWEEN '10/1/2007' and '01/01/2008'
      WHERE Client.Company Like 'DEMO'
      GROUP BY Client.Customer_No, Client.City,WashRun.LF_Desc 
)t
INNER JOIN
(
 SELECT t1.City,t1.Target_Weight,COUNT(*)+1 AS 'RowNo' FROM
 (
    Select Client.Customer_No, Client.City,
    WashRun.LF_Desc AS Formula,
    SUM(WashRun.LF_WT_Target) AS Target_Weight
    FROM Client
    INNER JOIN WashRun 
    ON WashRun.Customer_No = Client.Customer_No 
    AND	WashRun.BOF_Date BETWEEN '10/1/2007' and '01/01/2008'
    WHERE Client.Company Like 'DEMO'
    GROUP BY Client.Customer_No, Client.City,WashRun.LF_Desc 
    ORDER By Client.City, Target_Weight DESC
  )t1
 WHERE t1.City=t.City
 AND t1.Target_Weight>t.Target_Weight
)tmp
ON tmp.City=t.City
AND tmp.Target_Weight=t.Target_Weight
WHERE tmp.RowNo<=3
ORDER By t.City, t.Target_Weight DESC

pootle_flump

1064 Posts

Posted - 2008-01-04 : 12:43:06

quote:
Originally posted by pootle_flump

Ken - remove the trailing dot from the link

Ooh - you fiend. You edited your post and there is no "Edited on..." info. I look even denser than usual.

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2008-01-04 : 12:49:25

I couldn't get that to work. First problem was that it could not use Order by in T1 subquery so I changed that to do a SELECT TOP 10000.

Then it says that "The column prefix 't' does not match with a table name or alias name used in the query.", and it' on the first line. I played with it for a while but could not get it to work. I think it's related to the T1 Where clause.

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2008-01-04 : 14:22:07

quote:
Originally posted by pootle_flump

quote:
Originally posted by pootle_flump

Ken - remove the trailing dot from the link
Ooh - you fiend. You edited your post and there is no "Edited on..." info. I look even denser than usual.

Yes, sorry. As the forum admin I can edit posts without leaving a trail

I usually add a note when I do it but just forgot this time.

=================================================
Creating tomorrow's legacy systems today. One crisis at a time.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-07 : 01:03:04

Refer point 2
http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx

Madhivanan

Failing to plan is Planning to fail

RogerSGI
Starting Member

9 Posts

Posted - 2008-01-07 : 15:33:42

I am looking at something similar but can't wrap how the Refer point 2
works.

I have a Material Number, Date, Remark.

From this record set I would like the MatNo and Remark field from the lastest date.

Sample data coming in.
MatNo Date Remark
64281 2007-12-28 00:00:00.000 27999
64281 2007-09-18 00:00:00.000 94235- 9-18-07
64281 2007-06-27 00:00:00.000 92947- 6-27-07
64281 2006-03-16 00:00:00.000 86665- 3-16-06
64281 2005-10-13 00:00:00.000 85211 / 10-13-05
64283 2007-10-18 00:00:00.000 94384 / 10/18/07
64283 2006-03-24 00:00:00.000 86701- 3-24-06
64283 2005-10-13 00:00:00.000 85211-- 10-13-05
65692 2007-10-18 00:00:00.000 94391- 10-18-07
65692 2007-06-27 00:00:00.000 92948- 6-27-07
65692 2007-05-08 00:00:00.000 92080- 5/08/07
67479 2007-11-05 00:00:00.000 94846
67479 2007-11-02 00:00:00.000 94846

Result data needed
64281 2007-12-28 00:00:00.000 27999
64283 2007-10-18 00:00:00.000 94384 / 10/18/07
65692 2007-10-18 00:00:00.000 94391- 10-18-07
67479 2007-11-05 00:00:00.000 94846

Any ideas?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-08 : 00:28:08

Select t1.* from table t inner join
(
Select Matno,max(date) as date from table group by Matno
) as t2
on t1.Matno=t2.Matno and t1.date=t2.date

Madhivanan

Failing to plan is Planning to fail

RogerSGI
Starting Member

9 Posts

Posted - 2008-01-08 : 14:09:51

Thanks this helped.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources