Fetch alternate row according to the column value

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

General SQL Server Forums

New to SQL Server Programming

Fetch alternate row according to the column value

Author

Topic

Tamilmannan
Starting Member

2 Posts

Posted - 2010-12-09 : 09:42:42

hi friends,

I have table in the below schema

Product Type
AAAA 1
BBBB 1
CCCC 0
DDDD 0
EEEE 1

I want to fetch table alternative according to the Type column value
like:

Product Type
AAAA 1
CCCC 0
BBBB 1
DDDD 0
EEEE 1

anyone have any idea

---
Regards,
Tamil

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-09 : 09:59:35

[code]
SELECT Product, Type FROM
(
SELECT *, ROW_NUMBER() OVER ( PARTITION BY Type ORDER BY ( SELECT 1 )) RowNo FROM YourTable
) A
ORDER BY RowNo DESC
[/code]

Vaibhav T

If I cant go back, I want to go fast...

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-09 : 10:01:11

doh - and I missed the from clause

select product, type
from
(
select product, type, seq = row_number() over (partition by type order by product)
from tbl
) a
order by seq, product

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2010-12-09 : 10:08:29

Can we do it the old way?

Something like



CREATE TABLE #myTable99(Product varchar(20), [Type] int)
GO

INSERT INTO #myTable99(Product, [Type])
SELECT 'AAAA', 1 UNION ALL
SELECT 'BBBB', 1 UNION ALL
SELECT 'CCCC', 0 UNION ALL
SELECT 'DDDD', 0 UNION ALL
SELECT 'EEEE', 1
GO

SELECT * -- a.Product AS Product_1, Product_2
	, (SELECT TOP 1 b.Product AS Product_2 FROM  #myTable99 b WHERE b.Product > a.Product) AS b_Product
  FROM #myTable99 a

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-09 : 10:11:45

The ordering of the resultset is the issue so nothing without an order by clause can possibly work.

Actually I think it should be

select product, type
from
(
select product, type, seq = row_number() over (partition by type order by product)
from tbl
) a
order by seq, type desc

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-12-09 : 10:25:50

quote:
Originally posted by X002548

Can we do it the old way?

No, you need to start learning this new-fangled (5 years old already!) stuff like ROW_NUMBER() and CTEs and other things that DB2 has had for over 10 years now. *cough*

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-09 : 10:44:38

In the hope that Joe doesn't pop back today:
I believe they are both ansi 99.
Maybe not in the form implemented though - but I think sql server came pretty close.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Tamilmannan
Starting Member

2 Posts

Posted - 2010-12-09 : 23:47:56

HI nigelrivett,

Thank you very much, It works like magic...

regards,
Tamil.

---
Regards,
Tamil

X002548
Not Just a Number

15586 Posts

Posted - 2010-12-09 : 23:59:52

quote:
Originally posted by robvolk

quote:
Originally posted by X002548

Can we do it the old way?
No, you need to start learning this new-fangled (5 years old already!) stuff like ROW_NUMBER() and CTEs and other things that DB2 has had for over 10 years now. *cough*

OR we can STILL start begin to INSTILL the understanding that the ORDER of DATA in a DATABASE has no RELEVANT Meaning, except as defined by relationships and constraints

THAT'S OUR JOB

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2010-12-10 : 00:02:26

oh...and "learning" the "NEW" way

Oracle has had this crap for years

just stay true to Edgar

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-10 : 03:57:23

quote:
Originally posted by X002548

quote:
Originally posted by robvolk

quote:
Originally posted by X002548

Can we do it the old way?
No, you need to start learning this new-fangled (5 years old already!) stuff like ROW_NUMBER() and CTEs and other things that DB2 has had for over 10 years now. *cough*

OR we can STILL start begin to INSTILL the understanding that the ORDER of DATA in a DATABASE has no RELEVANT Meaning, except as defined by relationships and constraints

THAT'S OUR JOB

Data in a database doesn't have order but a returned resultset always does - whether it is explicit or implicit. This is about the resultset not the storage.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2010-12-10 : 10:19:36

It's also about a relationship between rows that does not exist

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-10 : 10:25:29

quote:
Originally posted by X002548

It's also about a relationship between rows that does not exist

If you'd said it's about trying to retrofit a business rule that should probably have been included in the database design then I'd agree:).
(The underlying cause of the effect of your statement).

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources