Multiple columns sort

[muskie]

I have a table with 6 columns: ID, Name, Date1, Date2, Date3, Date4
Each of the date fields is a different date, and they are not in
chronological order...that is, Date1 could be earlier or later that
Date2 and so on.

I need to pull the top ten records sorted by the most recent dates,
whether that be in Date1, Date2, Date3, or Date4.

I've tried a union join (below), where I join the data in four select
statements, giving each Date column a common FieldName....then I select
from that, sorting by date....then I select from that using distinct to
get only one of each record. If I don't use distinct, I get records
showing up more than once.

This however produces a recordset that is no longer ordered by LDate.

Any other ideas on how to go about this?
Thanks!
Matt

"SELECT DISTINCT TOP 10 ID, Name FROM (" &_
"SELECT * FROM (" &_
"SELECT Name,ID,Date1 AS LDate FROM TheTable" &_
" UNION " &_
"SELECT Name,ID,Date2 AS LDate FROM TheTable" &_
" UNION " &_
"SELECT Name,ID,Date3 AS LDate FROM TheTable" &_
" UNION " &_
"SELECT Name,ID,Date4 AS LDate FROM TheTable" &_
") ORDER BY LDate DESC" &_
");"

 

[John Vinson]

I have a table with 6 columns: ID, Name, Date1, Date2, Date3, Date4

What's the distinction of meaning between these dates? I strongly
suspect that your table could be better normalized, e.g. by having a
second table with fields ID, DateType, TheDate - with four records
rather than four fields.

Each of the date fields is a different date, and they are not in
chronological order...that is, Date1 could be earlier or later that
Date2 and so on.

I need to pull the top ten records sorted by the most recent dates,
whether that be in Date1, Date2, Date3, or Date4.

I've tried a union join (below), where I join the data in four select
statements, giving each Date column a common FieldName....then I select
from that, sorting by date....then I select from that using distinct to
get only one of each record. If I don't use distinct, I get records
showing up more than once.

Odd. Unless there are records with Date1 equal to Date4, say, the
UNION operator should remove the duplicates.

This however produces a recordset that is no longer ordered by LDate.

Any other ideas on how to go about this?

All I can suggest is basing a Top 10 query on the stored UNION query.

John W. Vinson[MVP]

 

[muskie]

Thanks John,

What's the distinction of meaning between these dates? I strongly
suspect that your table could be better normalized, e.g. by having a
second table with fields ID, DateType, TheDate - with four records
rather than four fields.

It could be better normalized I realize, but that's going to have to be
down the road...right now I need a temporary solution...and I think I
am going to get it through working with the recordset programmatic ally
after I get the data. I've got this working now, so maybe I'll just
stick with it. I wanted to see if there was an easy way to do it in
SQL, see if I was missing something.

Odd. Unless there are records with Date1 equal to Date4, say, the
UNION operator should remove the duplicates.

I wondered about that, but I think it's because the dates are different

All I can suggest is basing a Top 10 query on the stored UNION query.

Tried that but it won't give me top ten, still gives me more..

 

[David F Cox]

It looks like bad design to me too. Meanwhile ...
you could try sorting on something like:

IIF (date1>date2, date1,date2) > IIF(date3>date4,
date3,date4),IIF(date1>date2, date1,date2),IIF(date3>date4, date3,date4))

which is intended to find the greatest date.

 

[Douglas J. Steele]

Tried that but it won't give me top ten, still gives me more..

From the Help file:

"The TOP predicate doesn't choose between equal values. In the preceding
example, if the twenty-fifth and twenty-sixth highest grade point averages
are the same, the query will return 26 records."