TOP query with multiple columns
Here is the table I am using. I want query for this. I tried with many queries but in vain.
Topics XX YY ZZ RR ------ ---- ---- ---- ---- ABCD 120 25 40 65 EFGH 10 15 200 40 IJKL 35 250 125 145 MNOP 45 300 275 160
I want overall Top 'N' values for the above table. The maximum value in this table is 300, next is 275, next is 250, and so on. I want the result as follows:
MNOP YY 300 MNOP ZZ 275 IJKL YY 250 EFGH ZZ 200 MNOP ZZ 160 IJKL RR 145 IJKL ZZ 125 ABCD XX 120 ABCD RR 65 ...
You'll need a UNION query to produce a result set which contains all the values from each of the 4 columns in one result set column. Then you can do your "top N" query on the result set (which is called a derived table when used as a subquery).
Note that "top N" logic varies from one database to the next; see FIRST N rows, TOP N rows, LAST N rows, BOTTOM N rows...
Here's how it would look in SQL Server:
select top 10 Topics, source, sourcevalue from ( select Topics , 'XX' as source, XX as sourcevalue from yourtable union all select Topics, 'YY', YY from yourtable union all select Topics, 'ZZ', ZZ from yourtable union all select Topics, 'RR', RR from yourtable ) as dt order by sourcevalue desc
Note UNION ALL instead of UNION, because we do not want the result set scanned for duplicates.
Dig Deeper on SQL Server Database Modeling and Design
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.
Meet all of our SQL Server experts
View all SQL Server questions and answers
Start the conversation
0 comments