stange WHERE clause - SQL Server Forums

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

Transact-SQL (2005)

stange WHERE clause

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-09-07 : 18:03:06

Hi,

I have a query that brings back results according to a column "genderID" This is either a "1" or a "2".

Is there a way to write the query so every 4th record is a 1, and every other record is a 2 ?

I don't even know where to start on something like this, if its even possible (without creating a massive mess)

Thanks again!
mike123

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-07 : 19:06:43

Please post some sample data and the expected result.

What do you mean by 4th record ?

What is the sequence of ordering of record ?

KH
[spoiler]Time is always against us[/spoiler]

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-09-07 : 20:37:54

hi Khtan,

Here is some sample data, basically it is doing an order by on "DATE DESC". It is returning 4 genderID =2's and then a genderID =1 for every fifth record. It's a bit difficult to explain, so hopefully you follow me :)

thanks very much!!
mike123

SELECT userID,genderID from tblUserDetails

ORDER BY ????????? date DESC

The results could look like this

userID / genderID / date

114 / 2 / 09-15-2006
113 / 2 / 09-14-2006
112 / 2 / 09-13-2006
111 / 2 / 09-12-2006
103 / 1 / 09-04-2006
110 / 2 / 09-11-2006
109 / 2 / 09-10-2006
108 / 2 / 09-09-2006
107 / 2 / 09-08-2006
102 / 1 / 09-03-2006

tblUserDetails Sample Data

userID / genderID / date

100 / 1 / 09-01-2006
101 / 1 / 09-02-2006
102 / 1 / 09-03-2006
103 / 1 / 09-04-2006
104 / 2 / 09-05-2006
105 / 2 / 09-06-2006
106 / 2 / 09-07-2006
107 / 2 / 09-08-2006
108 / 2 / 09-09-2006
109 / 2 / 09-10-2006
110 / 2 / 09-11-2006
111 / 2 / 09-12-2006
112 / 2 / 09-13-2006
113 / 2 / 09-14-2006
114 / 2 / 09-15-2006




CREATE TABLE [dbo].[tblUserDetails](
	[UserID] [int] IDENTITY(1,1) NOT NULL,
	[GenderID] [tinyint] NULL,
	[Date] [datetime] NULL
	)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-07 : 22:37:41

[code]CREATE TABLE [dbo].[#tblUserDetails]
(
[UserID] [int] NOT NULL,
[GenderID] [tinyint] NULL,
[Date] [datetime] NULL
)

INSERT INTO #tblUserDetails
SELECT 100 , 1 , '09-01-2006' UNION ALL
SELECT 101 , 1 , '09-02-2006' UNION ALL
SELECT 102 , 1 , '09-03-2006' UNION ALL
SELECT 103 , 1 , '09-04-2006' UNION ALL
SELECT 104 , 2 , '09-05-2006' UNION ALL
SELECT 105 , 2 , '09-06-2006' UNION ALL
SELECT 106 , 2 , '09-07-2006' UNION ALL
SELECT 107 , 2 , '09-08-2006' UNION ALL
SELECT 108 , 2 , '09-09-2006' UNION ALL
SELECT 109 , 2 , '09-10-2006' UNION ALL
SELECT 110 , 2 , '09-11-2006' UNION ALL
SELECT 111 , 2 , '09-12-2006' UNION ALL
SELECT 112 , 2 , '09-13-2006' UNION ALL
SELECT 113 , 2 , '09-14-2006' UNION ALL
SELECT 114 , 2 , '09-15-2006'

SELECT *,
CASE
WHEN GenderID = 2 THEN seq + seq / 4
WHEN GenderID = 1 THEN (seq + 1) * 5
END AS new_seq
FROM
(
SELECT UserID, GenderID, [Date],
seq = row_number() OVER (PARTITION BY GenderID ORDER BY [Date] DESC) - 1
FROM #tblUserDetails
) a
ORDER BY new_seq, seq

/*

UserID GenderID Date seq new_seq
----------- -------- ------------ ------ -----------
114 2 2006-09-15 0 0
113 2 2006-09-14 1 1
112 2 2006-09-13 2 2
111 2 2006-09-12 3 3
103 1 2006-09-04 0 5
110 2 2006-09-11 4 5
109 2 2006-09-10 5 6
108 2 2006-09-09 6 7
107 2 2006-09-08 7 8
102 1 2006-09-03 1 10
106 2 2006-09-07 8 10
105 2 2006-09-06 9 11
104 2 2006-09-05 10 12
101 1 2006-09-02 2 15
100 1 2006-09-01 3 20

(15 row(s) affected)
*/
DROP TABLE #tblUserDetails[/code]

KH
[spoiler]Time is always against us[/spoiler]

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-08 : 14:01:11

why is the gender value dependent on the ordering of rows?
just curious

--------------------
keeping it simple...

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-09-08 : 21:28:03

hey khtan, thanks very much !! I will try this out , and post results :)

jen, I want to bring back a mixture of male and female profiles on this query, with more females ..I figure this would be a decent way to do it , open to any other suggestions as well

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-09 : 01:59:08

maybe for some matching services ? with gender ratio of 1:5 ?

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources