SQL problem!

[GzArIa]

Hi there hope someone can help me,

I have got following table

ID Cont-ID Date Status
24 69 06/02/2004 1
22 69 12/02/2004 6
23 69 13/02/2004 2
21 69 19/02/2004 1
25 71 05/05/2004 2
26 71 16/05/2004 1
27 71 17/05/2004 3
28 71 18/05/2004 7
29 71 31/05/2004 3
30 71 01/06/2004 2
31 71 03/06/2004 5
32 71 10/06/2004 4
33 71 17/06/2004 2
34 71 20/06/2004 3
35 71 21/06/2004 7
36 71 02/07/2004 3
37 71 04/07/2004 2
38 71 23/07/2004 6
39 71 25/07/2004 2
46 65 01/08/2004 2
45 69 07/08/2004 2
40 71 16/08/2004 1
41 71 17/08/2004 3
42 71 18/08/2004 7
43 71 31/08/2004 3
44 71 01/09/2004 2
47 61 04/09/2004 2
48 61 05/09/2004 3
17 69 15/09/2004 7
18 69 16/09/2004 3
19 69 19/09/2004 7



What I want to get is a list of Cont-IDs where the MAX date has got the Status 2. What I mean is I don't want the Max date where the status ID = 2 I want to know if the last date of each Cont-ID has got the status 2, otherwise I don't want it in the list. In this case Cont-ID is in the list cause the last date has got status 2 but 69 is not in there as the last entry has got status 7. Hope someone can help me.

Thanks,...
GzAriA

[raf]

Use a HAVING-clause. Like

SELECT cont-ID, max(Date), status FROM table GROUP BY cont-ID, status HAVING status=2

[GzArIa]

hi there, thanks for the quick help,

well that's actually how far I got as well; the problem is now that I get the results

Cont-ID Date Status
61 04/09/2004 2
65 01/08/2004 2
69 07/08/2004 2
71 01/09/2004 2

having a look at the list, we can see that the first record, 61, date 04/09/2004 is actually not the last entry as there is another one the day after, and that's what I need,

in short:
I need the MAX(date) of each Cont-Id. Then I check the status, if the status = 2 display it, if not then I don't need it, the results here should be,..

65 01/08/2004 2
71 01/09/2004 2

as these are the last entries and they also have status 2.

Hope I am not too complicated,...
thanks again
GzArIa

[raf]

indeed. she status can't be in the returned records (cause it'll need to be used for the grouping, which is not what you want).

i don't immedeately see a way to do it in one query, unless you use a subquery:

SELECT Cont_ID FROM table WHERE ID in (SELECT ID, Cont_ID, date FROM table GROUP BY ID, Cont_ID) AND status=2

[GzArIa]

thanks again,

a subquery solution wouldn't be a problem, I see where you are going, only problem is, SQL Server appearently doesn't allow more than one row to be selected in the subquery.

Err: Msg:Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

any help there?
I'll continue trying

[Roelf]

hi,
it easy to select all the cont_id's together with the max date for that cont_id, like

select cont_id, max(date)
from theTable
group by cont_id


but that way you'll have all possible maxdates, regardless of the value for the status column. But, you can inner join this resultset to the original table on equal cont_id and date fields to get access to the status field belonging to this combination of cont_id an date. Combine with a where status=2 would give the desired results.

I don't have mssql at my computer at the moment, but this description should give you some food for experiments. If you dont fix it by tomorrow morning (my timezone) i will build the sql for you and post it here.

Good luck trying, see you tomorrow

[raf]

i don't think it's possible in one query, so you'll either need to proces the results from your first query and build a collection of ID's that you can then use instead of the subselect
or
you need to work with a temporarely table where you dump the results from the first query, and join with it.

[Roelf]

this works in MSSQL

SELECT test.id, test.cont_id, test.[date], test.status
FROM test
INNER JOIN
(SELECT cont_id AS help_id, MAX([date]) AS maxdate
FROM test
GROUP BY cont_id) helpset
ON test.cont_id = helpset.help_id AND test.[date] = helpset.maxdate
WHERE (status = 2)

[GzArIa]

that's good stuff,...

thanks a lot seems to work fine,...

can you explain me what the helpset does, what it actually is,...
temp variable, alias ?

thanks again, you helped me a lot,...

GzArIa

[Roelf]

the part between () is the subquery which i mentioned in my previous post, to get the last date for each cont_id. I gave the resultset of this subquery an alias so i can refer to it later, in this case the alias was "helpset", but you can give it any name. This resultset can be handled like a table afterwards, so i joined it with the original table, which gives me exactly those records for which the date equals the last date. Now i can get access to the columns in the original table, like status and id

[raf]

very nice :thumbsup: