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  |
|