December 21, 2015 at 2:57 am
I have a table - thoughts
CREATE TABLE [dbo].[TRP_thoughts](
[thoughtid] [int] IDENTITY(1,1) NOT NULL,
[thoughts] [varchar](1500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[thoughtsof] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_thoughts] PRIMARY KEY CLUSTERED
(
[thoughtid] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
I want to display thought of the day in my website. Each day one thoguht should display as random. One day full that particular thought should display.
The next day another thought (random wise)should display the whole day.
how to write query for that? could anyone please help
December 21, 2015 at 3:12 am
Sounds like a homework question. Here's a hint - order by NEWID().
John
December 21, 2015 at 3:14 am
DECLARE @MaxThought INT;
SELECT @MaxThought = MAX(thoughtid) FROM TRP_thoughts;
SELECT * FROM TRP_thoughts WHERE thoughtid = FLOOR(RAND(CHECKSUM(NEWID()))*@MaxThought)+1;
Should get you a random row each time, and is more efficient than the usual TOP (1) ... ORDER BY NewID(). If there's gaps in the identity sequence you could end up not getting a row sometimes though.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply