Query to display records in the order of weekday desc

[vwani]

Hello friends,

I have a problem. I have this table X which has fields Col1,Col2,ColDate.
The data in all these fields can be duplicate except in ColDate.eg
Col1 Col2 ColDate
1234 MName 10/23/2004 11:00:00 AM
3456 MName 10/17/2004 9:00:00 AM
1234 MName 10/24/2004 11:00:00 AM

Then I have a stored procedure SP_Retrive with which the distinct values of X are joined with Left Join on Col1.
So that when I display records only one instance of 1234 comes up. But I want to display the data in descending order of weekday of ColDate.
e.g. if Oct 23 is Saturday for 1234 then it should appear above 3456 which has date of Oct 17 but that is Sunday.

How do I do this.
The distinct values from table X are retrived using foll View.
SELECT DISTINCT
Col1,Col2
FROM X
WHERE ColDate >= (getdate()- 0.083333333)

I tried to order this view using Top 100 Percent. But then it does not select Distinct values. This causes the records to appear more than ones.
Help!!

vw.

 

[rac2]

I think you just need to drop the DISTINCT qualifier. Then you have two rows for the two dates.

So there must be more to the story. Why are you joining only the distinct rows? What is in the table that is joined to x?

 

[vwani]

If I drop the distinct qualifier the records will be repeated.As I have explained in that example above.I am joining table X to a stored procedure on Col1.
This stored procedure contains data that I can join with Col1. But if I drop distinct from the view that I have created it will repeat the match
eg table Y
has columns Col4 Col5 Col6 Col7 Col8.Now data in all these fields is unique.
I am joining table Y to table X with left join on Col1 = Col4 and Col2=Col5.
If there is duplicate data in Col X then it will be reproduced in the joint result.

 

[rac2]

Maybe you want the row from X which has the maximum date?

You could make a view of X which only has the rows with the maximum dates. And JOIN that one instead of X.

 

[PHV]

Have you tried to sort by ColDate modulo 7 ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244