Create a view in sql with a column that counts

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

Create a view in sql with a column that counts

Author

Topic

windcent
Starting Member

6 Posts

Posted - 2014-11-05 : 12:58:35

I need to create a view that counts in one of the colums.
The tricky part for me is that it should only count to three, then start on 1 again and count to three again.

Example
1 - car
2 - bike
3 - Motorbike
1 - Boat
2 - Airplane
3 - Motorboat
1 - Bicycle

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-05 : 13:36:07

Please post you SQL Query for the view, minus the counting column

windcent
Starting Member

6 Posts

Posted - 2014-11-05 : 14:11:00

USE [Database]
GO

/****** Object: View [dbo].[viewTest] Script Date: 05.11.2014 18:37:11 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

ALTER view [dbo].[viewTest] AS

SELECT
ol.OrdNo, ol.ProdNo, ol.Descr,ol.DPrice, pr.PictNo, pr.NoteNm as DescriptLong

From ordln ol

left join Prod pr on ol.ProdNo = pr.prodno

GO

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-05 : 17:00:29

OK -- so which column is Motorbike in and which column determines that the count should begin at 1 again?

windcent
Starting Member

6 Posts

Posted - 2014-11-05 : 17:18:12

The view
SELECT
ol.OrdNo, ol.ProdNo, <<Counting>>
100 - car - 1
123 - fish - 2
100 - bike - 3
200 - Lion - 1
100 - Motorbike - 2
100 - Boat - 3
200 - Dog - 1
100 - Airplane - 2
100 - Motorboat - 3
100 - Bicycle - 1

If i make a select from the view with a where
Select <<count>>, ol.ProdNo from the db.view where ol.OrdNo = 100

I would like to have the result:
1 - car
2 - bike
3 - Motorbike
1 - Boat
2 - Airplane
3 - Motorboat
1 - Bicycle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources