SQL ordering question 1

[StewartUK]

I have a table with fields ShowDate & EditDate.

Here's a selection of records:

ShowDateTime          EditDateTime
a 06/03/2008 00:00:00,/  /       :  :
b 03/03/2008 00:00:00,/  /       :  :
c 06/03/2008 00:00:00,06/03/2008 15:30:55
d 03/03/2008 00:00:00,06/03/2008 11:20:22
e 03/03/2008 00:00:00,03/03/2008 11:18:54
f 03/03/2008 00:00:00,03/03/2008 11:17:39
g 03/03/2008 00:00:00,03/03/2008 11:16:24
h 03/03/2008 00:00:00,03/03/2008 11:18:24
i 03/03/2008 00:00:00,03/03/2008 11:17:54
j 03/03/2008 00:00:00,03/03/2008 11:17:20
k 03/03/2008 00:00:00,03/03/2008 11:16:31
l 03/03/2008 00:00:00,03/03/2008 11:16:03
m 06/03/2008 00:00:00,/  /       :  :
n 03/03/2008 00:00:00,04/03/2008 09:06:11
o 03/03/2008 00:00:00,03/03/2008 11:16:49
p 03/03/2008 00:00:00,03/03/2008 11:16:11

As you can see, you can have one record's EditDateTime being after another record's ShowDateTime. But then you can have another record that hasn't been edited, but the ShowDateTime is after another record's EditDateTime.

What I want to do is order them so that the record with the latest EditDateTime or ShowDateTime comes at the top, like this:

ShowDateTime          EditDateTime
c 06/03/2008 00:00:00,06/03/2008 15:30:55
d 03/03/2008 00:00:00,06/03/2008 11:20:22
a 06/03/2008 00:00:00,/  /       :  :
m 06/03/2008 00:00:00,/  /       :  :
n 03/03/2008 00:00:00,04/03/2008 09:06:11
e 03/03/2008 00:00:00,03/03/2008 11:18:54
h 03/03/2008 00:00:00,03/03/2008 11:18:24
i 03/03/2008 00:00:00,03/03/2008 11:17:54
f 03/03/2008 00:00:00,03/03/2008 11:17:39
j 03/03/2008 00:00:00,03/03/2008 11:17:20
o 03/03/2008 00:00:00,03/03/2008 11:16:49
k 03/03/2008 00:00:00,03/03/2008 11:16:31
g 03/03/2008 00:00:00,03/03/2008 11:16:24
p 03/03/2008 00:00:00,03/03/2008 11:16:11
l 03/03/2008 00:00:00,03/03/2008 11:16:03
b 03/03/2008 00:00:00,/  /       :  :

I can't think around this any more, can anyone point me in the right direction?

Thanks,

Stewart

 

[Mike Lewis]

Stewart,

Off the top of my ahead, how about something like this:

SELECT ShowDateTime, EditDateTime, OtherFields, ;
  IIF(ShowDateTime < EditDateTime, EditDatetime, ShowDateTime) AS SortKey .... ;
  ORDER BY SortKey

I am suggesting using IIF() rather than MAX() to get the later of the two dates, because MAX() behaves differently in a SQL statement.

Does this help at all?

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Stella740pl]

Did not test this, but did you try to index on MAX(EditDateTime, ShowDateTime)? May as well work.

 

[Stella740pl]

Mike,

I am suggesting using IIF() rather than MAX() to get the later of the two dates, because MAX() behaves differently in a SQL statement.

This is a good point.

 

[StewartUK]

Great - thanks for your help Mike & Stella. I actually woke up in the wee hours last night thinking along similar lines - sad but true!

Stewart

 

[Mike Lewis]

Stewart,

I actually woke up in the wee hours last night thinking along similar lines

You mean I'm not the only one who does that? I woke up early this morning thinking about drag-and-drop.

Glad the solution worked for you.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk