Q

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.

This was first published in January 2005

Dig deeper on SQL Server Database Modeling and Design

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close