April 17, 2013 at 8:17 am
Thanks, I learned something new today!
April 17, 2013 at 8:25 am
Nice one, thank you for posting.
(I knew the row constructor can be used in inserting value with single insert and the separated by comma, but never made an attempt to use the same with "select", so went with my assumption skills and picked the first one. This actually sent me back to the SQL stone-age . So simple thing and so powerful in action.)
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
April 17, 2013 at 9:54 am
This was removed by the editor as SPAM
April 17, 2013 at 10:16 am
Hmm... really interesting. Thanks, Chirag!
April 17, 2013 at 10:17 am
didn't run for me either, must be something with the versions of sql.
April 17, 2013 at 1:22 pm
Mike Dougherty-384281 (4/17/2013)
Hugo Kornelis (4/17/2013)
Good question. Not perfect, though.longer answer options - and especially the inclusion of three of the returned values for the "yes" option. Technically, this makes both answer options wrong. The query will return six "name" values, not just the three included in the answer.
Luckily this did not cause me to pick the wrong answer, but I think the question would have been better if either no results at all were included in the answer, or all, or the answer had included words like "among others" or so.
I did pick the wrong answer based on the fact that there would be 6 results. I've never seen the values clause in the context of a derived table, but there is enough meaning in the syntax to assume that if it works at all it would yield a cross product.
tbh, these "not perfect" QotD generate good discussion. 🙂
+1
April 17, 2013 at 3:57 pm
Could just be me, but ith the title Row Constructor I knew immediately that we were talking about SQL Server 2008 and newer as this was a new feature in SQL Server 2008.
I run into this at work since most of the development environments ar running SQL Server 2008 R2 Express Edition (Developers) or SQL Server 2008 R2 Developer Edition (DBA's) and we still run SQL Server 2005 in production (we are upgrading but it is taking time since our customer is the Government).
April 18, 2013 at 5:47 am
Raghavendra Mudugal (4/17/2013)
Nice one, thank you for posting.(I knew the row constructor can be used in inserting value with single insert and the separated by comma, but never made an attempt to use the same with "select", so went with my assumption skills and picked the first one. This actually sent me back to the SQL stone-age . So simple thing and so powerful in action.)
+1
AS raghu said used only in insert statement, Not used as derived table.
Good learning..
--
Dineshbabu
Desire to learn new things..
April 18, 2013 at 6:28 am
Looks as if there was a good idea but it was rather poorly executed. The query won't return 3 rows each with 1 column, but 6 rows each with two columns.
Why the particular 3 name values mentioned in the "correct" answer were chosen from the 6 available is a total mystery. Two of the result rows mentioned are derived from one row of the left hand input table but only one from the other, which is impossible from an outer join (ie no on clause) with no filter (ie no where clause) since the same number of output rows would be derived from each of the left hand rows. Equally impossible, of the 3 right hand input rows one contributes nothing to any output row while another contributes to 2 and 1 contributes to 1. otely correct
Teaching people that VALUES can be used to provide a table in the FROM clause is useful; but having only two answer options neither of which is remotely like the correct one is not really a good idea.
Tom
April 18, 2013 at 9:41 am
L' Eomot Inversé (4/18/2013)
Looks as if there was a good idea but it was rather poorly executed. The query won't return 3 rows each with 1 column, but 6 rows each with two columns.Why the particular 3 name values mentioned in the "correct" answer were chosen from the 6 available is a total mystery. Two of the result rows mentioned are derived from one row of the left hand input table but only one from the other, which is impossible from an outer join (ie no on clause) with no filter (ie no where clause) since the same number of output rows would be derived from each of the left hand rows. Equally impossible, of the 3 right hand input rows one contributes nothing to any output row while another contributes to 2 and 1 contributes to 1. otely correct
Teaching people that VALUES can be used to provide a table in the FROM clause is useful; but having only two answer options neither of which is remotely like the correct one is not really a good idea.
My intention was not to give wrong answers as options. but Just wanted to let the person give some idea that what if we use two VALUES statements in SELECT, should we get the error, or we can get the answers.
I could have write the Yes and No, instead of giving these Options.
Apologies for the wrong impressions..
April 22, 2013 at 6:32 am
Interesting question. Will never use it in production, but interesting nonetheless 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 22, 2013 at 3:30 pm
love it;-)
May 2, 2013 at 2:32 am
This is pure shot in the dark and luckily I got it right. Sincerely new to me but glad to know. +1
May 13, 2013 at 12:41 am
kapil_kk (4/17/2013)
New thing to learn... 🙂
Whilst I agree, I wonder if anyone has ever used this idea in a real piece of coding? The values are hard-coded and that's something I try my hardest to avoid.
May 13, 2013 at 2:13 am
marlon.seton (5/13/2013)
kapil_kk (4/17/2013)
New thing to learn... 🙂Whilst I agree, I wonder if anyone has ever used this idea in a real piece of coding? The values are hard-coded and that's something I try my hardest to avoid.
I'll have to reply with a bit of yes and a bit of no.
No, I have not (yet??) used a cross join between two hard-coded lists of constant in real work. Not saying it will never happen, though!
No, I have not used the exact syntax used in this question, but that is for backwards compatibility reasons only.
Yes, I have used a hardcoded source of rows in a query. The context was an "instead of" trigger. There was a requirement that the "previous" version of each row should also be in the table, but this was implement transparently to the front end. So the front end would just insert a row, and the instead of insert trigger would ensure that in reality, two versions of the row were inserted - the "current" and the "previous" version. At the heart of this trigger was a statement that went somewhat like this:
INSERT INTO TheTable(Version, Col1, Col2, ...)
SELECT x.Version, i.Col1, i.Col2, ...
FROM inserted AS i
CROSS JOIN
(SELECT 'current'
UNION ALL
SELECT 'previous') AS x(Version);
If I had to build this now, and I didn't have to provide support for SQL Server 2005 and older, I would replace the UNION ALL subquery with a VALUES clause as in this question.
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply