Create a view in sql with a column that counts

Question

Post reply

Create a view in sql with a column that counts

windcent

SSC Rookie

Points: 26
More actions
November 5, 2014 at 3:02 pm

#312377

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
----- Example of the View---
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

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101202 More actions · Answer 1

I don't know exactly how you want to order the results, but something like this should do what you want:

SELECT

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

(ROW_NUMBER() OVER(ORDER BY ol.OrdNo, ol.ProdNo) - 1) % 3 + 1 AS Counter

From ordln ol

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

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

windcent SSC Rookie Points: 26 More actions · Answer 2

windcent

SSC Rookie

Points: 26

November 5, 2014 at 3:21 pm

#1756830

deleted...

windcent SSC Rookie Points: 26 More actions · Answer 3

yes that was a good start,

The sorting is

(ROW_NUMBER() OVER(ORDER BY ol.OrdNo, ol.LnNo)

But the problem, it should start on 1 for each break in ol.OrdNo.

Example: see attached image.

Here is three orders, the first order has 6 lines... so it goes from 1-3 two times, the next order has only one line gets 1, the next order has two lines and get 2 and 3

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

windcent (11/5/2014)
yes that was a good start,
The sorting is
(ROW_NUMBER() OVER(ORDER BY ol.OrdNo, ol.LnNo)
But the problem, it should start on 1 for each break in ol.OrdNo.
Example: see attached image.
Here is three orders, the first order has 6 lines... so it goes from 1-3 two times, the next order has only one line gets 1, the next order has two lines and get 2 and 3

Here is a snippet :

ROW_NUMBER() OVER(PARTITION BY ol.OrdNo ORDER BY ol.LnNo)

windcent SSC Rookie Points: 26 More actions · Answer 5

windcent

SSC Rookie

Points: 26

November 5, 2014 at 4:06 pm

#1756848

that did the magic!

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 6

Just for fun, a computed column solution:-)

😎

USE tempdb;

GO

SET NOCOUNT ON;

CREATE TABLE dbo.TBL_COUNT_TO_THREE

(

CTT_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_COUNT_TO_THREE_CTT_ID PRIMARY KEY CLUSTERED

,CTT_VAL VARCHAR(50) NOT NULL

,CTT_COUNTER AS ((CTT_ID + 2) % 3 + 1) PERSISTED

);

INSERT INTO dbo.TBL_COUNT_TO_THREE (CTT_VAL)

VALUES ('1st VALUE'),('2nd VALUE'),('3rd VALUE'),('4th VALUE'),('5th VALUE'),('6th VALUE');

SELECT * FROM dbo.TBL_COUNT_TO_THREE;

DROP TABLE dbo.TBL_COUNT_TO_THREE;

Results

CTT_ID CTT_VAL CTT_COUNTER

----------- ------------ -----------

1 1st VALUE 1

2 2nd VALUE 2

3 3rd VALUE 3

4 4th VALUE 1

5 5th VALUE 2

6 6th VALUE 3