Ask the Expert

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 ...

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: