How to distinctly identify a record

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

SQL Server Development (2000)

How to distinctly identify a record

Author

Topic

jpiscit1
Posting Yak Master

130 Posts

Posted - 2004-04-02 : 14:06:00

I have a query that sums defects by item number. As follows:

Select Sum(low_lip)as [Low lip], item_no as [Item No]
From prod_data
Group By item_no

This produces something like:

Low lip Item No
2231 ISI0094A
0
1 ISI0320
0 ISI0096
0 ISI0068
24 ISI0301
0 ISI0172B
0 ISI0311
0 ISIOO74E
22 ISI0291
9 D-50
19 ISI0236D
1 ISI0100B
249 ISI0027

I modified the query so that I can find out which item number is producing the most defects, as such:

Select max(d.[Low lip]) as [lowlip]
From
(
Select Sum(low_lip)as [Low lip], item_no as [Item No]
From prod_data
Group By item_no
) d
Order by [lowlip] desc

With the result set above this will produce:

lowlip
2231

how can I modify this query so that it would give the same results but also include the Item no? For example, the result I want is:

lowlip Item No
2231 ISI0094A

Ultimately showing me which item no had the most defects...

Thanks in advance.

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-04-02 : 14:44:34

Here goes:

SELECT	TOP 1 [Low lip], [item no]
FROM (
Select Sum(low_lip)as [Low lip], item_no as [Item No]
From prod_data
Group By item_no
) x
ORDER BY [low lip] DESC

jpiscit1
Posting Yak Master

130 Posts

Posted - 2004-04-02 : 15:49:46

Great thanks ! That worked

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources