August 21, 2008 at 9:38 am
Seems like such a simple request... I need to select every other row. Essentially what I have is 2 tables on a report. The left table I would like to display every other row, and the right table will display every OTHER row. So if I selected out every other row as an expression, and then selected out every OTHER row as another expression, I could use those 2 expressions on the seperate tables right? Maybe... at any rate I could not locate the information anywhere. If someone knows something off the top of their head it would be appreciated. Thanks guys.
August 21, 2008 at 9:52 am
You can do it using the row_number() function if you are using SQL 2005 or 2008
this selects all odd records
;WITH ODD
AS
(SELECT ROW_NUMBER() OVER( ORDER BY NAME) AS Num
FROM MASTER.dbo.spt_values AS n
)
SELECT TOP 10 * FROM ODD
WHERE num % 2 != 0
this selects even records
;WITH EVEN
AS
(SELECT ROW_NUMBER() OVER( ORDER BY NAME) AS Num
FROM MASTER.dbo.spt_values AS n
)
SELECT TOP 10 * FROM EVEN
WHERE num % 2 = 0
August 21, 2008 at 9:58 am
Thanks a lot! I am a little miffed as to where I insert this code into my current query... just so you know here is my query:
SELECT ISNULL(CAB.Line1, '') + ', ' + ISNULL(CAB.Line2, '') + ', ' + ISNULL(CAB.City, '') + ', ' + ISNULL(CAB.StateOrProvince, '') + ' ' + ISNULL(CAB.PostalCode,
'') AS address, A.Name, CASE WHEN A.primarycontactid = CB.contactid THEN CB.fullname ELSE '' END AS primcontact,
CASE WHEN A.primarycontactid = CB.contactid THEN CB.emailaddress1 ELSE '' END AS primcontactemail,
(SELECT TOP 1 CB.FullName
FROM DPOB_MSCRM.dbo.ContactExtensionBase CEB INNER JOIN
DPOB_MSCRM.dbo.ContactBase CB ON CEB.ContactId = CB.ContactId AND CEB.new_dpobcontact = 1 AND CB.AccountId = A.AccountId)
AS dpobcontact,
(SELECT TOP 1 CB.emailaddress1
FROM DPOB_MSCRM.dbo.ContactExtensionBase CEB INNER JOIN
DPOB_MSCRM.dbo.ContactBase CB ON CEB.ContactId = CB.ContactId AND CEB.new_dpobcontact = 1 AND CB.AccountId = A.AccountId)
AS dpobcontactemail, A.Telephone1, A.Fax, A.WebSiteURL
FROM AccountBase A INNER JOIN
ContactBase CB ON CB.ContactId = A.PrimaryContactId INNER JOIN
CustomerAddressBase CAB ON A.AccountId = CAB.ParentId
ORDER BY A.Name
Sorry for the formatting.. Visual Studio does funky things to formatting. So I need the columns that I am selecting out to return even rows into one expression, and odd into another. I would need that for each column that I am selecting out.
August 21, 2008 at 10:55 am
Insert the whole thing into a temp table with an identity column. That way you only have to select it once, which will be better performance overall.
Then select from the temp table "where id%2 = 0" to get the even numbered ones, and "where id%2=1" to get the odd numbered.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 21, 2008 at 10:56 am
Actually, you might want to use row_number() when you insert into the temp table, to force the sequence. Just add ", row_number() over (order by A.Name) as Row" to the select. But do use a temp table, so you can run the overall select once.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 21, 2008 at 11:11 am
Thank you sir!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply