select last row and duplicate it

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

select last row and duplicate it

Author

Topic

roipatrick
Starting Member

3 Posts

Posted - 2013-10-03 : 03:05:29

here's my code (it has view so bear with me)

SELECT
ROW_NUMBER() OVER(ORDER BY SIGN_ON_DATE DESC) AS Row,
a.crew_no,
a.VESS_NAME,
a.VETY_DESCRIPTION,
a.POSI_ABBREVIATION,
CONVERT(VARCHAR, a.SIGN_ON_DATE, 103) AS SIGN_ON_DATE,
CASE WHEN a.SIGN_OFF_DATE IS NULL THEN
'PRESENT'
ELSE
CONVERT(VARCHAR, a.SIGN_OFF_DATE, 103)
END AS SIGN_OFF_DATE,
a.SIGN_ON_DATE AS ORDERDATE
FROM VW_CertSeaService1 AS a
WHERE a.crew_no = 29361
ORDER BY ORDERDATE desc

so after that it will look like this
1 29361 ATLANTA EXPRESS (ex Ludwigshafen Express) CONTAINER CHIEF COOK 25/11/2012 09/06/2013 2012-11-25 00:00:00.000
2 29361 KIEL EXPRESS CONTAINER CHIEF COOK 01/02/2012 26/10/2012 2012-02-01 00:00:00.000
3 29361 BONN EXPRESS CONTAINER CHIEF COOK 04/04/2011 26/10/2011 2011-04-04 00:00:00.000

so what i want it is to select the last row and duplicate it to have 6 rows. So if it only has 2 output the last row will be duplicated 4 times and 3 times if the result is 3 times.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-10-03 : 03:13:24

just CROSS JOIN to a table with 2 rows and you will get your duplicate
example

CROSS JOIN (select n = 1 union all select n = 2 ) n

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

roipatrick
Starting Member

3 Posts

Posted - 2013-10-03 : 03:17:26

quote:
Originally posted by khtan

just CROSS JOIN to a table with 2 rows and you will get your duplicate
example
CROSS JOIN (select n = 1 union all select n = 2 ) n

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

can you make it more clear sir. I want to select the last row of the result and duplicate it to make 6 rows.
so if 1 row = duplicate by 5
2 rows = duplicate by 4 and so on

roipatrick
Starting Member

3 Posts

Posted - 2013-10-03 : 03:32:48

quote:
Originally posted by khtan

just CROSS JOIN to a table with 2 rows and you will get your duplicate
example
CROSS JOIN (select n = 1 union all select n = 2 ) n

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

here's what i want to happen

row crew_no vessel description abbreviation SON SOF order
1 2345 AB AB AB 1 1 1
2 3456 CD CD CD 2 2 2
2 3456 CD CD CD 2 2 2
2 3456 CD CD CD 2 2 2
2 3456 CD CD CD 2 2 2
2 3456 CD CD CD 2 2 2

SEE THAT NUMBER 2 IS DUPLICATED 4 TIMES ASIDE FROM THE ORIGINAL AFTER NUMBER 1.

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-10-03 : 04:03:15

looks ugly


SIGN_ON_DATE varchar(10),
SIGN_OFF_DATE varchar(10),
ORDERDATE datetime)

insert into @Sample select
1,	29361,	'ATLANTA EXPRESS (ex Ludwigshafen Express)',	'CONTAINER',	'CHIEF COOK',	'25/11/2012',	'09/06/2013',	'2012-11-25 00:00:00.000' union all select
2,	29361,	'KIEL EXPRESS',					'CONTAINER',	'CHIEF COOK',	'01/02/2012',	'26/10/2012',	'2012-02-01 00:00:00.000' union all select
3,	29361,	'BONN EXPRESS',					'CONTAINER',	'CHIEF COOK',	'04/04/2011',	'26/10/2011',	'2011-04-04 00:00:00.000'

;WITH LastRow AS(
	SELECT
		MAX(Row)Row
	FROM	@Sample
), Duplicate AS(
	SELECT 
		1 Rows, Row, crew_no, VESS_NAME, VETY_DESCRIPTION, POSI_ABBREVIATION, SIGN_ON_DATE, SIGN_OFF_DATE, ORDERDATE
	FROM	@Sample
	UNION ALL
	SELECT 
		Rows + 1, Sample.Row, Sample.crew_no, Sample.VESS_NAME, Sample.VETY_DESCRIPTION, Sample.POSI_ABBREVIATION, Sample.SIGN_ON_DATE, Sample.SIGN_OFF_DATE, Sample.ORDERDATE
	FROM	@Sample Sample 
		JOIN LastRow
		ON Sample.Row = LastRow.Row
		JOIN Duplicate
		ON Duplicate.Rows < LastRow.Row
)
SELECT	DISTINCT *
FROM	Duplicate

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-10-03 : 04:17:57

[code]
DECLARE @Sample TABLE(
row int, crew_no int, vessel varchar(2), description varchar(2), abbreviation varchar(2), SON int, SOF int, [order] int)
INSERT INTO @Sample SELECT
1, 2345 ,'AB','AB','AB', 1, 1, 1 UNION ALL SELECT
2, 3456 ,'CD','CD','CD', 2, 2, 2

SELECT *
FROM @Sample
UNION ALL
SELECT Sample.*
FROM @Sample Sample
CROSS APPLY (
SELECT *
FROM (SELECT 1 Dup
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
)Duplicate
WHERE Sample.row <= Duplicate.Dup
AND Sample.row = (SELECT MAX(row) FROM @Sample)
)Duplicate
~~WHERE row = (SELECT MAX(row) FROM @Sample)~~
[/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-10-03 : 05:17:37

I see what you want . . . try


; with cte as
(
	SELECT
		ROW_NUMBER() OVER(ORDER BY SIGN_ON_DATE DESC) AS Row,
		ROW_NUMBER() OVER(ORDER BY ORDERDATE) AS RN,
		a.crew_no,
		a.VESS_NAME,
		a.VETY_DESCRIPTION,
		a.POSI_ABBREVIATION,
		CONVERT(VARCHAR, a.SIGN_ON_DATE, 103) AS SIGN_ON_DATE,
		CASE WHEN a.SIGN_OFF_DATE IS NULL THEN
			'PRESENT'
		ELSE
			CONVERT(VARCHAR, a.SIGN_OFF_DATE, 103)
		END AS SIGN_OFF_DATE,
		a.SIGN_ON_DATE AS ORDERDATE
	FROM	VW_CertSeaService1 AS a
	WHERE	a.crew_no = 29361
	ORDER BY ORDERDATE desc
),
num as
(
	select	n = 1 union all
	select	n = 2 union all
	select	n = 3 union all
	select	n = 4 union all
	select	n = 5 union all
	select	n = 6
)
select	*
from	cte c
	outer apply
	(
		select	n
		from	num n
		where	(c.RN > 1 and n = 1)
		or	(c.RN = 1 and n <= 6 - (select max(RN) from cte) + 1)
	) n

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources