SQL query question... 1

[janne11]

Hi,

I was hoping someone here is able to help me out, more of a general SQL query maybe but it is on a 10g database!

Some what simplified I have two tables:

PACKAGES
id name desc
----------------------
1 namea aaaaaa
2 nameb bbbbbb
3 namec cccccc
4 named dddddd


EVENTS
id code date
----------------------
1 A 20070103
1 B 20070104
1 X 20070106
2 B 20070107
2 C 20070106
3 A 20070108
3 B 20070108
3 C 20070109
3 D 20070109
3 X 20070110
4 X 20070112



For each package I want to retrieve the earliest event date from that packages events. But only for event of code A,B or C.

So the result should come out something like:

RESULTSET
id date
----------------
1 20070103 (code A)
2 20070106 (code C)
3 20070108 (code A and B)
4 (no code of type A,B or C)


I was hoping this would be possible using one query as opposed to doing an extra query (in the java code)
for each PACKAGE to get the EVENT time...

Stored procs are not an option
Any help appreciated.
Cheers!
Jan

 

[Welshbird]

Jan - just to be clear - is your date stored in this format or is it actually a date field?

If it is a date field then a simple joined query grouped with a min(date) where event in ('A','B','B') ought to give you what you need.

If not then you might need someone with more knowledge than I!

But, using your simplified example above you are only getting results from table EVENTS:

something like this?

select id, min(date) from EVENTS
where code in ('A','B','C')
group by ID

would seem to work, so I guess I am over simplifying the answer.

What else do you need to show?

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[janne11]

Thanks, unfortunately I simplified the example too much. Need more data from PACKAGE table. So the result should be something like

RESULTSET
id desc date
----------------------
1 aaaaaa 20070103 (code A)
2 bbbbbb 20070106 (code C)
3 cccccc 20070108 (code A and B)
4 dddddd (no code of type A,B or C)

And even worse the date is stored like the example as a varchar(8)...

thanks!

 

[hoinz]

Jan,

for clarification:
Are your dates in format YYYYMMDD or YYYYDDMM ?
In the first case min will give you the correct first date, even with varchar.
Otherwise it will be a little bit tricky ...

 

[Dagon]

select id, desc, date
from
(select p.id, p.desc, e.date, row_number() over
(partition by p.id order by e.date desc) as rn
from packages p, events e
where e.id = p.id
and e.code in ('A','B', 'C'))
where rn= 1

If the date is a character in the YYYYDDMM format, you can just use:

select id, desc, date
from
(select p.id, p.desc, e.date, row_number() over
(partition by p.id order by to_date(e.date, 'YYYYDDMM') desc) as rn
from packages p, events e
where e.id = p.id
and e.code in ('A','B', 'C'))
where rn= 1

 

[Dagon]

Since you want the earliest date, it should be "ASC" rather than "DESC" in the above.

 

[janne11]

hoinz: Its YYYYMMDD so should be ok with min.
Dagon: Thanks, interesting! Ill try it out now.

It is actually a more complicated query, it should also get the highest date where code is X,Y,Z and a few more things. But this gives me something to start with!