Select from Select? 1

[Stinney]

I can do this in Oracle but having a hard time with Informix (sql v7.32)

I have a database where each login id is assigned a unique record number (seq_num) when the login logs into a system.

The issue is that data in other fields can change each time the log in.

So if I select all the fields I need to look at I get multiple records if I do the following query because split can change values:

select max(seq_num) as seq_num, logid, split from haglog
group by logid, split
order by logid

But if I change it to:

select max(seq_num) as seq_num, logid from haglog
group by logid
order by logid

I just get the last login seq_num, which is what I want, but I don't get the split information.


So I want to do a select of my select something like:

select seq_num, logid, split from haglog
where seq_num [in/exists?](select max(seq_num) as seq_num, logid from haglog
group by logid)




- Stinney

Quoting only proves you know how to cut and paste.

 

[PHV]

SELECT seq_num, logid, split
FROM haglog H
WHERE seq_num=(SELECT MAX(seq_num) FROM haglog WHERE logid=H.logid)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Stinney]

Unfortunately, this causes the query to time out even if I increase the timer to 5 minutes.


- Stinney

Quoting only proves you know how to cut and paste.

 

[PHV]

And this ?

SELECT H.seq_num, H.logid, H.split
FROM haglog H INNER JOIN (
SELECT logid,MAX(seq_num) AS MaxSeq FROM haglog GROUP BY logid
) M ON H.logid=M.logid AND H.seq_num=M.MaxSeq

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Stinney]

It works! Thank you, thank you, thank you!

- Stinney

Quoting only proves you know how to cut and paste.