query i cant get my head round...

Question

Post reply

query i cant get my head round...

ben potts

Old Hand

Points: 384
More actions
April 19, 2006 at 2:47 pm

#112764

can you help me create this query im having a problem, i cant quite get my head round it.

i have a view...

http://img446.imageshack.us/img446/5760/newview8dm.jpg

the columns im interested in are

ConsultancyID (primary key)

ConsultancyBookingNumber (unique per booking. there are several rows for each ConsultancyBookingNumber, but each one has a different ConsultancyStateID)

ConsultancyStateID (step at which the booking is at. 1 just began, 10 confirmed)

ConsultancyID...ConsultancyBookingNumber...ConsultancyStateID

1.......................1............................................1

2.......................1............................................2

3.......................1............................................7

4.......................2............................................1

5.......................2............................................3

6.......................3............................................1

7.......................3............................................4

I have a simple query

SELECT distinct(consultancybookingnumber),max(consultancystateid)

FROM ben_vw_consultancy_history

GROUP BY consultancybookingnumber

which outputs one row per ConsultancyBookingNumber, and this row has the highest ConsultancyStateID for that ConsultancyBookingNumber.

The query outputs rows 3, 5 and 7 (correctly)

i need to alter the query to output only the corresponding ConsultancyID of the above rows, and also to exclude a booking number if the highest ConsultancyStateID for it is 4.

so in the above example the new query will only output the ConsultancyIDs 5 + 3.

i hope this makes sense!

any help is appreciated,

ben

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

Select q.consultancybookingnumber, q.ConsultancyStateID, h.ConsultancyID

from

(select consultancybookingnumber, max(consultancystateid) as ConsultancyStateID

from ben_vw_consultancy_history

group by consultancybookingnumber

) q

join ben_vw_consultancy_history h

on q.consultancybookingnumber = h.consultancybookingnumber

and q.ConsultancyStateID = h.ConsultancyStateID

hth

* Noel

Eddie Wuerch SSChampion Points: 12504 More actions · Answer 2

If I'm reading this right...

You don't need the DISTINCT statement, the GROUP handles that.

To filter on aggregates after the grouping, use the HAVING clause:

SELECT hist.ConsultancyID
  FROM  ben_vw_consultancy_history hist INNER JOIN
        (SELECT ConsultancyBookingNumber, 
                MAX(ConsultancyStateID) AS [ConsultancyStateID]
           FROM ben_vw_consultancy_history
          GROUP BY consultancybookingnumber
         HAVING MAX(ConsultancyStateID) != 4) agg 
                ON hist.ConsultancyBookingNumber = agg.ConsultancyBookingNumber AND
                   hist.ConsultancyStateID = agg.ConsultancyStateID

-Eddie

Eddie Wuerch
MCM: SQL