Need help with Select Statement

[logictrap]

I have a series of records that contain a date field and a sequence field.

I want to be able to select the records and sort them with the most recent date first unless there a value other than 0 in the sequence field.

The default value in the sequence field is 0.

Restated the sort should be:

- Sort in ascending order by sequence number if sequence number is greater than zero

- Then sort by date in descending order

I'm really hoping there is a single select statement that can perform this.

Thanks

[Fumigator]

What you can do is select the sequence number inside an "if" statement, returning a value larger than any other sequence number if it's 0, then order by that column.

For example:


SELECT date_column,
if (sequence_nr = 0, 9999999999, sequence_nr) as seq_nr_zeros_last
FROM table1
ORDER BY seq_nr_zeros_last asc, date_column desc

[logictrap]

I am humbled by your expertise!

Thanks so much.

[guelphdad]

ORDER BY
CASE WHEN SEQUENCE > 0 THEN 0 ELSE 1 END,
SEQUENCE,
DATE DESC

[logictrap]

Must be my lucky day - two solutions and both work!

Thank you.