Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 467
  • Last Modified:

make SQL Server Query to prioritize / combine results from two other existing queries

Using SQL Server 2005, I have two working queries, and I'm trying to make a third that will prioritize them.  

The first query (QA) pulls the top 5 results (never more than 5 provided) based only on UnitID.  The second query (QB) pulls the top 9 results based on proximity (longitude and lattitude).   Since I have 10 total slots to fill on my results table, no more or less, with the third query (QC) I'm trying to pull as many results as QA has (it will vary from 0 - 5), then fill in the leftover slots with results from QB.  Existing queries follow:

QA:
<cfquery name="rsStatic" datasource="#application.constant.dsn#">
      SELECT l.*, p.PictType, p.PictID, p.Extension, p.Width, p.Height
      FROM Listings AS l
            LEFT OUTER JOIN Pictures AS p ON l.ListingID = p.ListingID AND p.PictType = 'SCPN' OR p.PictType = 'FCPN'
      WHERE l.UnitID = <cfqueryparam value="#Form.UnitID#" cfsqltype="cf_sql_bigint">
            AND p.PictID IS NOT NULL
</cfquery>


QB:
<cfquery name="rsDynamic" datasource="#application.constant.dsn#">
      SELECT TOP 10
            l.*, p.PictType, p.PictID, p.Extension,
            CASE WHEN ABS(Latitude - <cfqueryparam value="#rsStation.Latitude#" cfsqltype="cf_sql_float">) < ABS(Longitude - <cfqueryparam value="#rsStation.Longitude#" cfsqltype="cf_sql_float">)
                  THEN ABS(Longitude - <cfqueryparam value="#rsStation.Longitude#" cfsqltype="cf_sql_float">)
                  ELSE ABS(Latitude - <cfqueryparam value="#rsStation.Latitude#" cfsqltype="cf_sql_float">)
                  END AS Distance
      FROM Listings AS l
            LEFT OUTER JOIN Pictures AS p ON l.ListingID = p.ListingID AND p.PictType = 'NCPN'
      WHERE 1.0 > ABS(Latitude - <cfqueryparam value="#rsStation.Latitude#" cfsqltype="cf_sql_float">)
            AND 1.0 > ABS(Longitude - <cfqueryparam value="#rsStation.Longitude#" cfsqltype="cf_sql_float">)
            AND p.PictID IS NOT NULL
      ORDER BY Distance
</cfquery>

QC: ??
0
Upekrithen
Asked:
Upekrithen
  • 3
  • 3
1 Solution
 
jamcosCommented:
You can use Union for the 2 statements and select the top from that result. You probably need to add Width and Height as columns from QB to make the columns match. It would look like:
Select top 10 * from (<QA> union all <QB>) as unionedresult

Here's a specific example I tested on a database structure I have locally that returned all the query A results and as many query B results as needed to make a total of 10:
select top 10 * from (
select * from v_preflist where ownerid = '1295'
union all
select top 10 * from v_preflist where ownerid = 'co63') as myresult
0
 
UpekrithenAuthor Commented:
I've tried your suggestion with the results posted after this comment, and I think one reason it doesn't work is that I'm already using parentheses, so the code breaks after the first internal closing parentheses.  Also, isn't there a way to do a query on queries so that I could simply reference the other two queries?  Anyway, here is my non-working result from trying to implement your suggestion.  Thanks!

<cfquery name="rsCombined" datasource="#application.constant.dsn#">
      SELECT TOP 10 FROM (
      SELECT TOP 5
            l.*, p.PictType, p.PictID, p.Extension, p.Width, p.Height
      FROM Listings AS l
            LEFT OUTER JOIN Pictures AS p ON l.ListingID = p.ListingID AND p.PictType = 'SCPN' OR p.PictType = 'FCPN'
      WHERE l.UnitID = <cfqueryparam value="#Form.UnitID#" cfsqltype="cf_sql_bigint">
            AND p.PictID IS NOT NULL
            
      UNION ALL

      SELECT TOP 10
            l.*, p.PictType, p.PictID, p.Extension,
            CASE WHEN ABS(Latitude - <cfqueryparam value="#rsStation.Latitude#" cfsqltype="cf_sql_float">) < ABS(Longitude - <cfqueryparam value="#rsStation.Longitude#" cfsqltype="cf_sql_float">)
                  THEN ABS(Longitude - <cfqueryparam value="#rsStation.Longitude#" cfsqltype="cf_sql_float">)
                  ELSE ABS(Latitude - <cfqueryparam value="#rsStation.Latitude#" cfsqltype="cf_sql_float">)
                  END AS Distance
      FROM Listings AS l
            LEFT OUTER JOIN Pictures AS p ON l.ListingID = p.ListingID AND p.PictType = 'NCPN'
      WHERE 1.0 > ABS(Latitude - <cfqueryparam value="#rsStation.Latitude#" cfsqltype="cf_sql_float">)
            AND 1.0 > ABS(Longitude - <cfqueryparam value="#rsStation.Longitude#" cfsqltype="cf_sql_float">)
            AND p.PictID IS NOT NULL
            <!---AND Expires >= #now()#--->
      ORDER BY Distance)
</cfquery>
0
 
jamcosCommented:
I had the same thought about just selecting ... for whatever reason, SQL Server is picky and makes you name the parenthesized query ('as myresult' in my sample). I also noticed you are missing * in select top 10. I didn't setup a sample db to match your structure, but I believe this will work:

SELECT TOP 10 * FROM (
      SELECT TOP 5
            l.*, p.PictType, p.PictID, p.Extension, p.Width, p.Height
      FROM Listings AS l
            LEFT OUTER JOIN Pictures AS p ON l.ListingID = p.ListingID AND p.PictType = 'SCPN' OR p.PictType = 'FCPN'
      WHERE l.UnitID = <cfqueryparam value="#Form.UnitID#" cfsqltype="cf_sql_bigint">
            AND p.PictID IS NOT NULL
           
      UNION ALL

      SELECT TOP 10
            l.*, p.PictType, p.PictID, p.Extension,
            CASE WHEN ABS(Latitude - <cfqueryparam value="#rsStation.Latitude#" cfsqltype="cf_sql_float">) < ABS(Longitude - <cfqueryparam value="#rsStation.Longitude#" cfsqltype="cf_sql_float">)
                  THEN ABS(Longitude - <cfqueryparam value="#rsStation.Longitude#" cfsqltype="cf_sql_float">)
                  ELSE ABS(Latitude - <cfqueryparam value="#rsStation.Latitude#" cfsqltype="cf_sql_float">)
                  END AS Distance
      FROM Listings AS l
            LEFT OUTER JOIN Pictures AS p ON l.ListingID = p.ListingID AND p.PictType = 'NCPN'
      WHERE 1.0 > ABS(Latitude - <cfqueryparam value="#rsStation.Latitude#" cfsqltype="cf_sql_float">)
            AND 1.0 > ABS(Longitude - <cfqueryparam value="#rsStation.Longitude#" cfsqltype="cf_sql_float">)
            AND p.PictID IS NOT NULL
            <!---AND Expires >= #now()#--->
      ORDER BY Distance) as myresults
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
UpekrithenAuthor Commented:
Well, copied and pasted your code (basic difference being yes, the asterisk, plus two parentheses, so apparently the parentheses were not the problem), and I get this error:

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
0
 
jamcosCommented:
Ahh... that means we have the problem of needing to make sure the columns of the 2 queries match. The differences are that QA is including p.Width, p.Height and that QB is including Distance. So, I'd add a 'fake' Distance to the first and add the dimensions to the second:

SELECT TOP 10 * FROM (
      SELECT TOP 5
            l.*, p.PictType, p.PictID, p.Extension, p.Width, p.Height, 0.0 as Distance
      FROM Listings AS l
            LEFT OUTER JOIN Pictures AS p ON l.ListingID = p.ListingID AND p.PictType = 'SCPN' OR p.PictType = 'FCPN'
      WHERE l.UnitID = <cfqueryparam value="#Form.UnitID#" cfsqltype="cf_sql_bigint">
            AND p.PictID IS NOT NULL
           
      UNION ALL

      SELECT TOP 10
            l.*, p.PictType, p.PictID, p.Extension, p.Width, p.Height,
            CASE WHEN ABS(Latitude - <cfqueryparam value="#rsStation.Latitude#" cfsqltype="cf_sql_float">) < ABS(Longitude - <cfqueryparam value="#rsStation.Longitude#" cfsqltype="cf_sql_float">)
                  THEN ABS(Longitude - <cfqueryparam value="#rsStation.Longitude#" cfsqltype="cf_sql_float">)
                  ELSE ABS(Latitude - <cfqueryparam value="#rsStation.Latitude#" cfsqltype="cf_sql_float">)
                  END AS Distance
      FROM Listings AS l
            LEFT OUTER JOIN Pictures AS p ON l.ListingID = p.ListingID AND p.PictType = 'NCPN'
      WHERE 1.0 > ABS(Latitude - <cfqueryparam value="#rsStation.Latitude#" cfsqltype="cf_sql_float">)
            AND 1.0 > ABS(Longitude - <cfqueryparam value="#rsStation.Longitude#" cfsqltype="cf_sql_float">)
            AND p.PictID IS NOT NULL
            <!---AND Expires >= #now()#--->
      ORDER BY Distance) as myresults
0
 
UpekrithenAuthor Commented:
Cool!  I think that does it, testing aside.  Good job!!  Thanks for your quick response, and more importantly, your accuracy.  Thanks!

- J.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now