Problem solve
Get help with specific problems with your technologies, process and projects.
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.
