August 4, 2016 at 5:05 am
I just need at the end of the view remove the records where if StudentTransportOrder has two instances of the same VehicleID, keep record of the VehicleType = Car...so when the view is out putted the duplicates are removed and ,master tables remain unaffected - please help
record will look like
StudentTransportOrder View
StudentID, VehicleID, VehicleType
1 A1234 CAR
1 A1234 Train
I just need the record
StudentID, VehicleID, VehicleType
1 A1234 CAR
to remain in the view
August 4, 2016 at 5:17 am
joanna.seldon (8/4/2016)
I just need at the end of the view remove the records where if StudentTransportOrder has two instances of the same VehicleID, keep record of the VehicleType = Car...so when the view is out putted the duplicates are removed and ,master tables remain unaffected - please helprecord will look like
StudentTransportOrder View
StudentID, VehicleID, VehicleType
1 A1234 CAR
1 A1234 Train
I just need the record
StudentID, VehicleID, VehicleType
1 A1234 CAR
to remain in the view
Welcome to the forum. Could you post some sample data and the code you've already tried please? I can see what you're trying to do but without the base tables any answer will just be a guess. If you follow the link in my signature, you'll find a good article explaining how best to give us the samples.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
August 4, 2016 at 5:47 am
joanna.seldon (8/4/2016)
I just need at the end of the view remove the records where if StudentTransportOrder has two instances of the same VehicleID, keep record of the VehicleType = Car...so when the view is out putted the duplicates are removed and ,master tables remain unaffected - please helprecord will look like
StudentTransportOrder View
StudentID, VehicleID, VehicleType
1 A1234 CAR
1 A1234 Train
I just need the record
StudentID, VehicleID, VehicleType
1 A1234 CAR
to remain in the view
What logic should be used to determine whether it is CAR or Train which is returned?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 4, 2016 at 8:56 am
I basically wish to say if, you see if VehicleID >2 (Duplicate), keep record with VehicleType CAR and not return Train record
please help within the view
August 4, 2016 at 9:00 am
joanna.seldon (8/4/2016)
I basically wish to say if, you see if VehicleID >2 (Duplicate), keep record with VehicleType CAR and not return Train recordplease help within the view
What do you want to happen if there is more than one VehicleID and neither of them are for cars? Are both the VehicleIDs you've posted meant to be the same?
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
August 4, 2016 at 9:08 am
Look up ROW_NUMBER, specifically Example B: Returning a subset of rows. You want to return the subset where the ROW_NUMBER equals 1. You just have to define the correct partition and order in your ROW_NUMBER's OVER clause to get your desired results.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 4, 2016 at 4:05 pm
Here's a quick example of what Drew's talking about. Naturally adjust the ORDER BY clause as needed to get exactly the output you want:
IF OBJECT_ID('tempdb.dbo.#StudentTransportOrder') IS NOT NULL
DROP TABLE #StudentTransportOrder
CREATE TABLE #StudentTransportOrder (
StudentID int null,
VehicleID varchar(30) null,
VehicleType varchar(30) null
)
INSERT INTO #StudentTransportOrder
VALUES(1, 'A1234', 'CAR'),(1, 'A1234', 'Train'),(1, 'A1234', 'Bus'),
(2, 'B1234', 'Walk'),(2, 'B1234', 'Subway'),(2, 'B1234', 'Bus')
SELECT d1.StudentID, d1.VehicleID, d1.VehicleType
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY StudentID, VehicleID
ORDER BY CASE WHEN VehicleType = 'CAR' THEN 1 ELSE 2 END, VehicleType) AS row_num
FROM #StudentTransportOrder
) AS d1
WHERE row_num = 1
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
August 5, 2016 at 7:53 am
hi
this works brill within my view
, ROW_NUMBER() OVER(PARTITION BY a.StudentID
ORDER BY CASE WHEN b.[VehicleType] = 'CAR' THEN 1 ELSE 2 END, VehicleType) AS row_num
thank you
how do I at then end of the view , where you have there where
say
AND( , ROW_NUMBER() OVER(PARTITION BY a.StudentID
ORDER BY CASE WHEN b.[VehicleType] = 'CAR' THEN 1 ELSE 2 END, VehicleType) AS row_num
) =1)
PLEASE HELP
August 5, 2016 at 8:02 am
how do I at then end of the view , where you have there where
Can you clarify what you mean by this?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 5, 2016 at 8:06 am
hi
because I have made a column row_num, it does not recognise it if I just say at end of view
Where ArrivalDate Between '20110101' and '20111231'
and row_num = 1
says invalid column name
please help
August 5, 2016 at 8:08 am
joanna.seldon (8/5/2016)
hithis works brill within my view
, ROW_NUMBER() OVER(PARTITION BY a.StudentID
ORDER BY CASE WHEN b.[VehicleType] = 'CAR' THEN 1 ELSE 2 END, VehicleType) AS row_num
thank you
how do I at then end of the view , where you have there where
say
AND( , ROW_NUMBER() OVER(PARTITION BY a.StudentID
ORDER BY CASE WHEN b.[VehicleType] = 'CAR' THEN 1 ELSE 2 END, VehicleType) AS row_num
) =1)
PLEASE HELP
You MUST use a CTE (or derived table). In a simple query, the WHERE clause is evaluated before the SELECT clause, so it CANNOT reference the ROW_NUMBER which is defined in the SELECT clause. The CTE is completely evaluated (including the ROW_NUMBER specification) before it is used in the main query, so the ROW_NUMBER from the CTE is available in the main query WHERE clause.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 5, 2016 at 8:08 am
Probably this (?):
SELECT d1.StudentID, d1.VehicleID, d1.VehicleType
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY StudentID, VehicleID
ORDER BY CASE WHEN VehicleType = 'CAR' THEN 1 ELSE 2 END, VehicleType) AS row_num
FROM #StudentTransportOrder
WHERE ArrivalDate >= '20110101' AND ArrivalDate < '20120101'
) AS d1
WHERE row_num = 1
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
August 30, 2016 at 3:53 am
Hi
this worked !!! thank you
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply