August 28, 2013 at 1:16 am
I have the following two queries but how do I combine them to return the results into one row as one result set.
The result I'm Looking for would be
1 2013/08/23 00:00:00 5 2013/08/28 00:00:00
DECLARE @t TABLE (
[ndx] [int] IDENTITY(1,1) NOT NULL,
[value] [int] NULL,
[t_stamp] [datetime] NULL);
INSERT INTO @t VALUES (1,'20130823')
INSERT INTO @t VALUES (1,'20130824')
INSERT INTO @t VALUES (2,'20130825')
INSERT INTO @t VALUES (3,'20130826')
INSERT INTO @t VALUES (4,'20130827')
INSERT INTO @t VALUES (5,'20130828')
INSERT INTO @t VALUES (5,'20130829')
SELECT min(value), min(t_stamp) from @t
WHERE value = (SELECT min(value) FROM @t)
SELECT min(value), min(t_stamp) from @t
WHERE value = (SELECT max(value) FROM @t)
August 28, 2013 at 1:26 am
Probably several ways of doing that, but this one will work:
WITH a (aval, astamp) AS (
SELECT min(value), min(t_stamp) from @t
WHERE value = (SELECT min(value) FROM @t)
)
, b (bval, bstamp) AS (
SELECT min(value), min(t_stamp) from @t
WHERE value = (SELECT max(value) FROM @t)
)
SELECT
a.aval
,a.astamp
,b.bval
,b.bstamp
FROM
a
CROSS JOIN b
John
August 28, 2013 at 1:28 am
John's correct, here's another way:
SELECT
col1 = MIN(CASE WHEN t.value = x.min_value THEN value ELSE NULL END),
col2 = MIN(CASE WHEN t.value = x.min_value THEN t_stamp ELSE NULL END),
col3 = MIN(CASE WHEN t.value = x.max_value THEN value ELSE NULL END),
col4 = MIN(CASE WHEN t.value = x.max_value THEN t_stamp ELSE NULL END)
FROM @t t
CROSS APPLY (
SELECT min_value = MIN(value), max_value = MAX(value)
FROM @t
) x
WHERE t.value IN (x.min_value, x.max_value)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 30, 2013 at 3:28 am
Thanks for that. That turned out way more complex than I ever imagined for what appears (in theory) to be a simple retrieval of a couple of rows of data. Gonna have to study the solutions to try to understand them better.:-)
August 30, 2013 at 3:45 am
Here's another way which you might find a little more intuitive:
;WITH
q1 (min_value, min_t_stamp) AS (
SELECT min(value), min(t_stamp)
FROM @t
WHERE value = (SELECT min(value) FROM @t)
),
q2 (min_value, min_t_stamp) AS (
SELECT min(value), min(t_stamp)
FROM @t
WHERE value = (SELECT max(value) FROM @t)
)
SELECT *
FROM q1, q2
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 17, 2013 at 4:12 am
Old fashioned:
SELECT value, MIN(t_stamp)
FROM @t
WHERE value IN
(
SELECT MIN(Value) Value FROM @t UNION ALL
SELECT MAX(Value) FROM @t
)
GROUP BY value
________________________________________________________
If you set out to do something, something else must be done first.
September 18, 2013 at 11:45 pm
ChrisM@Work (8/28/2013)
John's correct, here's another way:
SELECTcol1 = MIN(CASE WHEN t.value = x.min_value THEN value ELSE NULL END),
col2 = MIN(CASE WHEN t.value = x.min_value THEN t_stamp ELSE NULL END),
col3 = MIN(CASE WHEN t.value = x.max_value THEN value ELSE NULL END),
col4 = MIN(CASE WHEN t.value = x.max_value THEN t_stamp ELSE NULL END)
FROM @t t
CROSS APPLY (
SELECT min_value = MIN(value), max_value = MAX(value)
FROM @t
) x
WHERE t.value IN (x.min_value, x.max_value)
+1 for a bizarre but wonderful query!
It inspired me to come up with this:
SELECT col1 = MIN(CASE WHEN a.t_stamp = mv1 THEN value ELSE NULL END),
col2 = MIN(CASE WHEN a.t_stamp = mv1 THEN t_stamp ELSE NULL END),
col3 = MIN(CASE WHEN a.t_stamp = mv2 THEN value ELSE NULL END),
col4 = MIN(CASE WHEN a.t_stamp = mv2 THEN t_stamp ELSE NULL END)
FROM (
SELECT value, t_stamp
,mv1=MIN(t_stamp) OVER (PARTITION BY (SELECT NULL))
,mv2=MAX(t_stamp) OVER (PARTITION BY (SELECT NULL))
FROM @t) a
WHERE t_stamp = mv1 OR t_stamp = mv2
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 19, 2013 at 12:42 am
So many replies,,,,,, so much of learning.! 🙂
September 19, 2013 at 1:53 am
OMG so many ways!!! This community is great 🙂
September 25, 2013 at 1:03 am
hi, check this query..
; with
RECORD_SET1(MIN_VALUE, MIN_TSTAMP)AS
(SELECT min(VALUE), min(T_STAMP) from @t
WHERE VALUE = (SELECT min(VALUE) FROM @t)),
RECORD_SET2(MAX_VALUE, MAX_TSTAMP)AS
(SELECT min(VALUE), min(T_STAMP) from @t
WHERE VALUE = (SELECT max(VALUE) FROM @t))
SELECT * FROM RECORD_SET1,RECORD_SET2;
September 26, 2013 at 3:52 pm
SELECT
a.mins, a.stamp, b.mins, b.stamp
FROM
(SELECT min(value) as mins, min(t_stamp) as stamp
from @t
WHERE value = (SELECT min(value) FROM @t)) a,
(SELECT min(value) as mins, min(t_stamp) as stamp
from @t
WHERE value = (SELECT max(value) FROM @t)) b
September 27, 2013 at 1:13 am
Gotta say I like that last one ccavaco. Very easy to understand. (in fact the ONLY one I understand 🙂 )
Thanks!
September 27, 2013 at 1:18 am
brett.y (9/27/2013)
in fact the ONLY one I understand
It's one of four very similar solutions that have been posted. The only ways they differ are as follows:
(1) Some use common table expressions (CTEs); some use subqueries
(2) Some use a comma to join the two tables; some use the CROSS JOIN operator
John
September 27, 2013 at 1:25 am
I'd bet money though that ChrisM's first solution, bizarre as it seems, is probably the fastest one suggested.
I had to dissect it myself before I figured out what he was doing. As you probably should. It is an excellent learning opportunity.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 27, 2013 at 1:56 am
As a NooB to SQL I do tend to like what I actually understand (it was just nice to see a solution I understood before I ran it etc) .......................but your 100% correct, every answer I work through brings me on in SQL. Thanks again for sharing your expertise.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply