max command

[grinder332518]

My file has 2 fields : seqno and date
There can be lots of records for each seqno.
For each seqno, the date can be either set, or nulls.

How do I use the max function to return only the highest seqno which has a date set ?
ie ignoring those where the highest seqno is null ?

Thanks

[JKing]

SELECT MAX(seqno)
FROM your_table
WHERE date IS NOT NULL

[grinder332518]

thanks J
This gives me one value for all the different sequence numbers.

If I have :-
ref seq date
1 1 1/1/11
1 2 1/3/11
1 3 1/5/11
2 1 2/1/11
2 2 null
3 1 2/10/11
3 2 2/11/11

then I would like the following as my output :-
1 3 1/5/11
3 2 2/11/11

Sorry if I wasn't clear enough earlier.

[JKing]

Does 2 1 2/1/11 also fit in that list?

Do you want the highest seqno for each ref where the date is not null?

[grinder332518]

Hi J
I do indeed only want the highest seqno for each ref where the date is not null. So ref 2 would be excluded entirely. Many thanks for your ongoing help.

[JKing]

Why would ref 2 be excluded? Should it not fallback to seqno 1 as it would be the highest seqno where the date is not null for that ref.

[grinder332518]

Hi J
It is to be exluded because that is how it is defined in my spec. Unfortunately, I do not know enough about the system to determine what you are suggesting is true or not. I would be most grateful therefore if you can give a suggestion based on Ref 2, and all similar cases, being excluded.
Many thanks again.

[JKing]

Fair enough.

SELECT your_table.ref, your_table.seqno, your_table.`date` 
FROM (
		SELECT ref, MAX(seqno) as seqno FROM your_table GROUP BY ref
	 ) AS sub_query
	 JOIN your_table ON your_table.ref = sub_query.ref AND your_table.seqno = sub_query.seqno
WHERE your_table.`date` IS NOT NULL

Change all references of your_table to your table's name.