Regarding fetch from the query...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Regarding fetch from the query...

Author

Topic

great_mamun
Starting Member

14 Posts

Posted - 2008-08-27 : 15:27:15

Dear All,
I have a query: "select id,name from tblCustomer where pdate='08/27/2008'"

Suppose it will return 10 records, and is there any procedure so that I will fetch 4th or 5th or 7th (only one but random) record.

Best Regards,
Abdullah Al Mamun

georgev
Posting Yak Master

122 Posts

Posted - 2008-08-28 : 05:23:35

From Jeff Smith's blog here on SQLTeam, I picked up this neat little trick
[CODE]
IF object_id('dbo.vRandNumber') IS NOT NULL BEGIN
DROP VIEW dbo.vRandNumber
END
GO
CREATE VIEW dbo.vRandNumber
AS
SELECT Rand() as RandNumber
GO

IF object_id('dbo.RandNumber') IS NOT NULL BEGIN
DROP FUNCTION dbo.RandNumber
END
GO
CREATE FUNCTION RandNumber()
RETURNS float
AS
BEGIN
RETURN (SELECT RandNumber FROM vRandNumber)
END
GO
[/CODE]
This allows you to then extend your query to return a single random result.
[CODE]
SELECT TOP 1 *
FROM (
SELECT id
, name
FROM tblcustomer --Why the "tbl" prefix? tblIt tblSeems tblKinda tblUseless
WHERE pdate = '20080827'
) x
ORDER
BY dbo.RandNumber()
[/CODE]

George