Solved

SQL Query with more that one "SELECT TOP 1"

Posted on 2011-09-11
4
233 Views
Last Modified: 2012-05-12
Greetings,
I am trying to generate a SQL query where I can select more than one "TOP 1".

For instance, I might want the top 1 from one column and the top 1 from another column, both being in different rows.

SELECT TOP 1 'Column1' as x, TOP 1 'Column3' as y, Z FROM TableA WHERE Z LIKE '%str%'

Please let me know if this requires more explanation.  Thanks in advance!
0
Comment
Question by:MaxKroy
  • 3
4 Comments
 
LVL 4

Accepted Solution

by:
degaray earned 500 total points
ID: 36520613
You can use two subqueries for that, see the code:

I hope it helps you

Cheers!
SELECT(
   SELECT TOP 1 'Column1' 
   FROM TableA WHERE Z LIKE '%str%)
   AS x, (
   SELECT TOP 1 'Column3'
   FROM TableA WHERE Z LIKE '%str%
   ) AS y

Open in new window

0
 
LVL 4

Expert Comment

by:degaray
ID: 36520624
Depending on the data on your tables you might need to add a LIMIT 1, 0

So this should be the code then.
SELECT(
   SELECT TOP 1 'Column1' 
   FROM TableA WHERE Z LIKE '%str% LIMIT 1,0
   ) AS x, (
   SELECT TOP 1 'Column3'
   FROM TableA WHERE Z LIKE '%str% LIMIT 1,0
   ) AS y

Open in new window

0
 
LVL 4

Expert Comment

by:degaray
ID: 36520627
Sorry like this:
SELECT(
   SELECT TOP 1 'Column1' 
   FROM TableA WHERE Z LIKE '%str% LIMIT 0, 1
   ) AS x, (
   SELECT TOP 1 'Column3'
   FROM TableA WHERE Z LIKE '%str% LIMIT 0, 1
   ) AS y

Open in new window

0
 

Author Closing Comment

by:MaxKroy
ID: 36520693
Thank you Obi-one. You are my only hope.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now