| Author |
 Topic  |
|
SwePeso
Patron Saint of Lost Yaks
Sweden
30421 Posts |
|
|
Seventhnight
Flowing Fount of Yak Knowledge
USA
2878 Posts |
|
|
DonAtWork
Flowing Fount of Yak Knowledge
2167 Posts |
|
|
robvolk
Most Valuable Yak
USA
15732 Posts |
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8781 Posts |
 Posted - 07/12/2011 : 10:41:29
|
Out of a table with 1000000 rows I want to select the customer with id 666 without using a where clause...
If that is too simple then I want it without SELECT...
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
robvolk
Most Valuable Yak
USA
15732 Posts |
Posted - 07/12/2011 : 11:08:11
|
C'mon Fred, that's easy:
1. bcp out the entire table
2. use VBScript/Powershell/findstr/sed to extract the row with ID 666
3. use sqlcmd to truncate the table, or create a new one
4. bcp in the single row to the appropriate table
See? No WHERE clause.  If you don't want a SELECT, just stop at step 2. |
|
|
|
RickD
Slow But Sure Yak Herding Master
United Kingdom
3608 Posts |
Posted - 07/12/2011 : 12:19:45
|
quote:
Originally posted by webfred
Out of a table with 1000000 rows I want to select the customer with id 666 without using a where clause...
If that is too simple then I want it without SELECT...
No, you're never too old to Yak'n'Roll if you're too young to die.
Surely just use ROW_NUMBER on the id (assuming you have one, select the top 666 in a sub select, then order desc and select the top1..  |
|
|
|
Seventhnight
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 07/12/2011 : 13:13:32
|
quote:
Originally posted by RickD
quote:
Originally posted by webfred
Out of a table with 1000000 rows I want to select the customer with id 666 without using a where clause...
If that is too simple then I want it without SELECT...
No, you're never too old to Yak'n'Roll if you're too young to die.
Surely just use ROW_NUMBER on the id (assuming you have one, select the top 666 in a sub select, then order desc and select the top1..
No no no... what if some of the ids below 666 had been deleted??
Select top 1 * From table Order By case when id=666 then 0 else 1 end
Corey
I Has Returned!! |
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8781 Posts |
Posted - 07/12/2011 : 13:51:46
|
I love you all - nerds 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
30421 Posts |
Posted - 07/13/2011 : 01:04:19
|
SELECT ID FROM dbo.Table1 GROUP BY ID HAVING ID = 666
N 56°04'39.26"
E 12°55'05.63" |
|
|
|
PSamsig
Constraint Violating Yak Guru
Denmark
384 Posts |
Posted - 07/13/2011 : 03:09:07
|
UPDATE C SET ID = C.ID
OUTPUT INSERTED.*
FROM dbo.Customer C
INNER JOIN (
SELECT ID = 666
) D
ON C.ID = D.ID
-- If you give someone a program, you will frustrate them for a day; if you teach them how to program, you will frustrate them for a lifetime. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
30421 Posts |
Posted - 07/13/2011 : 03:48:17
|
Replace INNER JOIN content with VALUES(666) and add a column alias for the derived table d, and you get an output for ID 666 without either select nor where.
UPDATE C SET ID = C.ID
OUTPUT INSERTED.*
FROM dbo.Customer C
INNER JOIN (
values(666)
) D(id)
ON C.ID = D.ID
N 56°04'39.26"
E 12°55'05.63" |
|
|
|
Sachin.Nand
Flowing Fount of Yak Knowledge
2937 Posts |
Posted - 07/13/2011 : 04:17:40
|
Select * From Table1 T1 Inner Join (Select 666 As ID)T On T.ID=T1.ID
PBUH
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
30421 Posts |
Posted - 07/13/2011 : 04:42:47
|
Still a SELECT.
N 56°04'39.26"
E 12°55'05.63" |
|
|
|
Sachin.Nand
Flowing Fount of Yak Knowledge
2937 Posts |
Posted - 07/13/2011 : 04:47:33
|
I was referring the initial requirement..The one without the where clause.
PBUH
|
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5155 Posts |
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8781 Posts |
Posted - 07/13/2011 : 05:13:47
|
quote:
Originally posted by sunitabeck
print 666 i cannot take all the credit for this innovative solution. It is based on an original algorithm developed in this thread. http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=159217
Sunita, I will have a Beck's on you
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5155 Posts |
Posted - 07/13/2011 : 07:12:16
|
Haha, Fred! You are the inventor of the algorithm, so by all means!!
(I thought you liked Weißbier, if I recall correctly!) |
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8781 Posts |
Posted - 07/13/2011 : 07:17:11
|
quote:
Originally posted by sunitabeck
(I thought you liked Weißbier, if I recall correctly!)
Oh no, that is a rumor strewn by DonAtWork
And since I know you I like Beck's
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
DonAtWork
Flowing Fount of Yak Knowledge
2167 Posts |
|
|
Topic |
|
Twit List