Multiple columns sort

  • Thread starter Thread starter muskie
  • Start date Start date
M

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" &_
");"
 
I have a table with 6 columns: ID, Name, Date1, Date2, Date3, Date4
Click to expand...

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.
Click to expand...

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?
Click to expand...

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

John W. Vinson[MVP]
 
Thanks John,

John said:
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.
Click to expand...

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.
Click to expand...
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.
Click to expand...
Tried that but it won't give me top ten, still gives me more..
 
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.
 
Tried that but it won't give me top ten, still gives me more..
Click to expand...

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."
 
Back
Top