• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 295
  • Last Modified:

SELECT question: Need entire row returned for group

Let's say I have a table called DOWNTIME.  Fields let's say are: LINE_NUM, SHIFT, REASON, DATECODE, PRODUCT, OPERATOR, FLAG1, FLAG2, FLAG3, NOTES, DOWNTIME

This table has 1000's of records accumulating DOWNTIME minutes of 12 separate production lines (LINE_NUM = 1,2,3,...12).

I want to return a 12 line recordset.
Each of the 12 records should be the record where the most recent DATECODE is found (per LINE_NUM).
I need *all of the fields* displayed for that one record.  (not just one or two fields using max(), group by clause)

Thanks.
0
deshi777
Asked:
deshi777
1 Solution
 
CarlWarnerCommented:
Can you use a TOP 12 clause based on the maximum DOWNTIME?
0
 
jrb1Commented:
select *
from downtime
where line_num + 12 > (select max(line_num) from downtime)
0
 
jrb1Commented:
oh, sorry...i see now

select *
from downtime a
where datecode = (select max(datecode) from downtime where line_num = a.line_num)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
lluthienCommented:
Jrb1 's  solution looks okay,

but i'm guessing it is not strict enough.
that selection doesn't take into account that there might be two line_nums with the same datecode.

i think mine does, although i don't have a query analyzer to verify this. sorry.

select distinct b.*
from downtime a
join downtime b  on
(
b.datecode = (select max(datecode) from downtime where line_num = b.line_num)
and b.line_num = a.line_num
)

i'm pretty sure there is a neater way to fix this,
but i think it should work.

cheers
0
 
jrb1Commented:
They don't care if there are 2 lines with the same date code.  There are 12 lines.  They want the highest date code for each of the 12 lines.
0
 
lluthienCommented:
my bad, jrb1

i read your where clause wrong
0
 
rmaranhaoCommented:
If you have an AutoInc PrimaryKey, then

select *
from downtime
where pk in
(select max(pk) from downtime group by linenum)

should be faster....


0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now