[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

getting the minum value of two select statements

Posted on 2014-03-07
4
Medium Priority
?
274 Views
Last Modified: 2014-03-07
I have these two select statements joined with a union statement and they return the correct values.  I would like to select the minimum of the two values?

      (select MIN(wou.use_date)
      from web_OnlinePass_Use wou
      inner join web_top_id wti on wti.ind_id = wou.id
      inner join Name n on n.ID = wou.id
      where wti.top_ID = '16040' and (USE_DATE >= '12/07/2012' and USE_DATE <= '12/06/2013'))
      union
      (select MIN(wolu.olu_date)
      from web_Online_library_Use wolu
      inner join web_top_id wti on wti.ind_id = wolu.id
      inner join Name n on n.ID = wolu.id
      where wti.top_ID = '16040' and (olu_DATE >= '12/07/2012' and olu_DATE <= '12/06/2013'))


need the figure how to nest this union as part of another select min() statement?
0
Comment
Question by:Jeff_Kingston
  • 3
4 Comments
 
LVL 11

Expert Comment

by:Simone B
ID: 39912754
Try this:

SELECT MIN(X.DateValue) FROM (
      (select MIN(wou.use_date) AS DateValue
      from web_OnlinePass_Use wou 
      inner join web_top_id wti on wti.ind_id = wou.id
      inner join Name n on n.ID = wou.id
      where wti.top_ID = '16040' and (USE_DATE >= '12/07/2012' and USE_DATE <= '12/06/2013'))
      union
      (select MIN(wolu.olu_date) AS DateValue
      from web_Online_library_Use wolu 
      inner join web_top_id wti on wti.ind_id = wolu.id
      inner join Name n on n.ID = wolu.id
      where wti.top_ID = '16040' and (olu_DATE >= '12/07/2012' and olu_DATE <= '12/06/2013'))
) AS X

Open in new window

0
 
LVL 11

Accepted Solution

by:
Simone B earned 2000 total points
ID: 39912770
You can also use a Common Table Expression:

(Edit sorry, forgot the brackets)

WITH CTE (DateValue) AS 
     ( (select MIN(wou.use_date) AS DateValue
      from web_OnlinePass_Use wou 
      inner join web_top_id wti on wti.ind_id = wou.id
      inner join Name n on n.ID = wou.id
      where wti.top_ID = '16040' and (USE_DATE >= '12/07/2012' and USE_DATE <= '12/06/2013'))
      union
      (select MIN(wolu.olu_date) AS DateValue
      from web_Online_library_Use wolu 
      inner join web_top_id wti on wti.ind_id = wolu.id
      inner join Name n on n.ID = wolu.id
      where wti.top_ID = '16040' and (olu_DATE >= '12/07/2012' and olu_DATE <= '12/06/2013')))
SELECT MIN( DateValue) FROM CTE

Open in new window

0
 

Author Closing Comment

by:Jeff_Kingston
ID: 39912774
Super thanks for the rapid reply.
0
 
LVL 11

Expert Comment

by:Simone B
ID: 39912776
You're welcome!
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

872 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