Solved

getting the minum value of two select statements

Posted on 2014-03-07
4
263 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 500 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

914 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

16 Experts available now in Live!

Get 1:1 Help Now