Answer Posted / rakesh kumar jaiswal

CREATE TABLE Names
(
NameID INT IDENTITY(1, 1)
PRIMARY KEY CLUSTERED,
FName VARCHAR(32)
)
GO

SET NOCOUNT ON
INSERT Names(FName) VALUES('Aaron')
INSERT Names(FName) VALUES('Greg')
INSERT Names(FName) VALUES('Alex')
INSERT Names(FName) VALUES('Luan')
INSERT Names(FName) VALUES('John')
INSERT Names(FName) VALUES('Todd')
INSERT Names(FName) VALUES('Scott')
INSERT Names(FName) VALUES('Jess')
INSERT Names(FName) VALUES('Drew')
INSERT Names(FName) VALUES('Katherine')
INSERT Names(FName) VALUES('Paul')
GO

-- solution #1: nested top

SELECT TOP 1 FName
FROM
(
SELECT TOP 10 FName
FROM Names
ORDER BY FName
) sub
ORDER BY FName DESC

-- solution #2: NOT IN

SELECT TOP 1 FName
FROM Names WHERE FName NOT IN
(
SELECT TOP 9 FName
FROM Names
ORDER BY FName
)
ORDER BY FName

-- solution #3: derived count
-- this assumes FName is unique

SELECT FName
FROM Names
WHERE
(
SELECT COUNT(*)
FROM Names n2
WHERE n2.FName <= Names.FName
) = 10

-- solution #4: MAX

SELECT FName = MAX(FName) FROM
(
SELECT TOP 10 FName
FROM Names
ORDER BY FName
) sub

-- solution #5: relative fetch from cursor

-- yes, cursors are generally evil, but
-- sometimes you might be surprised

DECLARE FNames CURSOR
LOCAL STATIC READ_ONLY FOR
SELECT FName
FROM Names
ORDER BY FName

DECLARE @FName VARCHAR(32)

OPEN FNames

FETCH RELATIVE 10 FROM FNames INTO @FName

CLOSE FNames
DEALLOCATE FNames

SELECT FName = @FName

DROP TABLE Names
GO

what is the difference between count(*) and count(1) ?

1065

---

What is delete query?

923

---

How to fetch the next row from a cursor with a "fetch" statement?

1053

---

What are the events recorded in a transaction log?

978

---

Are resultset updatable?

923

---

How to scale out a federation by Sql statement?

116

---

How to create user defined functions with parameters?

1205

---

What does asynchronous call backs means?

1128

---

Explain differentiate between a having clause and a where clause?

889

---

How to resolve the orphan use problem? : sql server security

996

---

How to stop a loop early with break statements in ms sql server?

995

---

How to turn off warning messages during php execution?

1019

---

What is the meaning of lock escalation and why/how to stop this? : sql server database administration

1018

---

What are the disadvantages of using querystrings to send data from one page to another?

976

---

How to connect to SQL Azure Database by using sqlcmd?

136

---