SQL to reorder data by column 1

[VickyC]

hello to all

I have tables that all look like this... [tt]

ID C1 C2 C3 C4 C5 C6
1 32 34 11 56 87 11
2 55 77 89 34 77 77
3 13 56 56 12 90 56
... etc [/tt]

I'm hoping to find SQL that will order each row's values in C1 to C6 in descending order. Some values may be repeated in a row.
The output would look like this... [tt]

ID Q1 Q2 Q3 Q4 Q5 Q6
1 87 56 34 32 11 11
2 89 77 77 77 55 34
3 90 56 56 56 13 12
... etc [/tt]

Thanks in advance for any advice.

Vicky c.

 

[dhookom]

I would use a normalizing union query with a ranking query to put the values in order for each ID. Then use a crosstab to get them back into an un-normalized format.

Duane
Hook'D on Access
MS Access MVP

 

[VickyC]

thanks Duane - I'm a bit fuzzy on the ranking part of your answer, but I'll give it a try.
Vicky C.

 

[dhookom]

Assuming a table name of TTVickyC and your data, try create these queries:

Union query to normalize your data:
ttuniVickyC

SELECT ID, 1 as C, C1 as TheValue
FROM TTVickyC
UNION ALL
SELECT ID, 2, C2
FROM TTVickyC
UNION ALL
SELECT ID, 3, C3
FROM TTVickyC
UNION ALL
SELECT ID, 4, C4
FROM TTVickyC
UNION ALL
SELECT ID, 5, C5
FROM TTVickyC
UNION ALL SELECT ID, 6, C6
FROM TTVickyC;

Ranking Query:
[ttgrpVickyCRank]

SELECT ttuniVickyC.ID, ttuniVickyC.C, ttuniVickyC.TheValue, Count(ttuniVickyC.ID) AS Rank
FROM ttuniVickyC INNER JOIN ttuniVickyC AS ttuniVickyC_1 ON ttuniVickyC.ID = ttuniVickyC_1.ID
WHERE ((([ttuniVickyC].[TheValue]*100+[ttuniVickyC].[C])<=[ttuniVickyC_1].[TheValue]*100+[ttuniVickyC_1].[c]))
GROUP BY ttuniVickyC.ID, ttuniVickyC.C, ttuniVickyC.TheValue
ORDER BY ttuniVickyC.ID, ttuniVickyC.TheValue DESC;

Final results crosstab query:

TRANSFORM Sum(ttgrpVickyCRank.TheValue) AS SumOfTheValue
SELECT ttgrpVickyCRank.ID
FROM ttgrpVickyCRank
GROUP BY ttgrpVickyCRank.ID
PIVOT "Q" & [Rank];

Duane
Hook'D on Access
MS Access MVP

 

[VickyC]

Duane - awesome! I was only stuck on the middle part (ranking), but I appreciate the full solution. It turns out I was also missing the ALL in the UNIONs, which would eventually have caused some grief.

Vicky C.

 

[dhookom]

I think the "1 as C" would alleviate the UNION ALL but I think it is more efficient withe the "ALL".



Duane
Hook'D on Access
MS Access MVP