March 3, 2016 at 5:06 am
Using SQL SERVER 2012 I created 2 tables: 1 containing cars and a second containing users of those cars. Now I want to see the last user for each car.
Using the following query I get however both users for car n° 2.
SELECT Cars.CarID, Cars.Make, Cars.Model, OwnerCar.Owner
FROM Cars INNER JOIN
OwnerCar ON Cars.CarID = OwnerCar.CarID
Result:
1 OPEL Combo Peeters
2 OPEL Combo Verbruggen
2 OPEL Combo Hollewegens
3 OPEL Insigna Debrabandere
Can anybody point me in the right direction please ?
Tnx.
March 3, 2016 at 5:12 am
What column in your table defines which user is the 'last' one?
Can you post table definitions and sample data please (as CREATE TABLE and INSERT statements)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 3, 2016 at 5:14 am
GilaMonster (3/3/2016)
What column in your table defines which user is the 'last' one?Can you post table definitions and sample data please (as CREATE TABLE and INSERT statements)
When I clicked Reply it was appeared :laugh: so same like above... I think that you have to have some timestamp or for example Identity ID and there can be order by biggest
March 3, 2016 at 5:31 am
Table 1:
USE [TEST]
GO
/****** Object: Table [dbo].[Cars] Script Date: 3/03/2016 13:27:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cars](
[CarID] [int] NOT NULL,
[Make] [nvarchar](50) NULL,
[Model] [nvarchar](50) NULL,
CONSTRAINT [PK_Cars] PRIMARY KEY CLUSTERED
(
[CarID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
and is containing:
1 OPEL Combo
2 OPEL Combo
3 OPEL Insigna
Table 2:
USE [TEST]
GO
/****** Object: Table [dbo].[OwnerCar] Script Date: 3/03/2016 13:27:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[OwnerCar](
[OwnerID] [int] NOT NULL,
[CarID] [int] NULL,
[Owner] [nvarchar](50) NULL,
[EndDate] [date] NULL,
CONSTRAINT [PK_OwnerCar] PRIMARY KEY CLUSTERED
(
[OwnerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
and is containing:
1 1 Peeters 1900-01-01
2 2 Verbruggen 2015-09-30
3 2 Hollewegens 1900-01-01
4 3 Debrabandere 1900-01-01
indicating a car still in use is 1900-01-01 (no date)
March 3, 2016 at 5:35 am
Cool, and what determines the user that you want to see? for each car?
The one with 1900-01-01?
The one with the highest Enddate?
The one with the lowest Enddate?
Something else?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 3, 2016 at 6:01 am
In this case for car n° 2 I only want to see Hollewegens as being the current car user.
I need to do this without using the date or anything else.
March 3, 2016 at 6:02 am
What defines Hollewegens as the user you want to see?
To write a query we need a method to determine which user should be shown, based on columns in the User table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 3, 2016 at 6:11 am
GilaMonster (3/3/2016)
What defines Hollewegens as the user you want to see?To write a query we need a method to determine which user should be shown, based on columns in the User table.
I think that he want, if 1900-01-01 is there will be showed this, if 1900-01-01 will not be there ,will be showed highest date.
March 3, 2016 at 6:12 am
tony28 (3/3/2016)
GilaMonster (3/3/2016)
What defines Hollewegens as the user you want to see?To write a query we need a method to determine which user should be shown, based on columns in the User table.
I think that he want, if 1900-01-01 is there will be showed this, if 1900-01-01 will not be there ,will be showed highest date.
That's what I just asked and the answer was
I need to do this without using the date or anything else.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 3, 2016 at 6:13 am
GilaMonster (3/3/2016)
tony28 (3/3/2016)
GilaMonster (3/3/2016)
What defines Hollewegens as the user you want to see?To write a query we need a method to determine which user should be shown, based on columns in the User table.
I think that he want, if 1900-01-01 is there will be showed this, if 1900-01-01 will not be there ,will be showed highest date.
That's what I just asked and the answer was
I need to do this without using the date or anything else.
:laugh: right
March 3, 2016 at 6:28 am
Hi guys, Tnx for the efforts.
Right now I get 2 users, namely Verbruggen and Hollewegens for car n° 2.
I don't want to see Verbruggen in that result. The date is only entered when the car is no longer in use by someone.
March 3, 2016 at 6:35 am
Yes, we get that you don't want to see Verbruggen and you do want to see Hollewegens, but what we're asking is what values of columns determine that Hollewegens is the one you want to see?
If the date is only entered when the car is no longer in use, does that mean you want to see the car along with the user of that car who has an end date of 1900-01-01?
If not, then what specifically defines which row you want to see and which you don't?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 3, 2016 at 6:36 am
You can't really figure out what the "last" user is without some method of indicating the order of the users. You have to have a date or a sequence number or something that allows you to establish an order on the data. You can't simply rely on the last value added to the table will show up last. Without something establishing an ORDER BY value, you can't guarantee an order.
Once you have an ORDER BY value, then you just have to SELECT TOP 1 to get the last or latest or whatever based on the order you establish.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 3, 2016 at 6:46 am
Ok, let's try this
INSERT INTO Cars (CarID, Make, Model)
VALUES (1, 'Opel', 'Corsa'), (2, 'VW','Polo'), (3, 'BMW', 'Z4')
INSERT INTO CarOwners (OwnerID, CarID, Owner, EndDate)
VALUES (1,1,'Pam','2014-02-15'), (2,1,'Bob', '2015-10-25'), (3,2,'Sam', '2016-01-01'), (4,1,'John','2016-02-29'), (5,3,'Mark', '2016-03-03'), (6, 2, 'Tim', '1900-01-01'), (7, 1, 'Matt', '1900-01-01')
Which of the users should show against the cars?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 3, 2016 at 2:04 pm
marc.persiau (3/3/2016)
indicating a car still in use is 1900-01-01 (no date)
It is a BAD IDEA to use a PAST date to represent an unknown FUTURE date. You're much better off using something far in the future. I tend to use '9999-12-30', but I know that other people use '9000-01-01' or '9999-01-01'. Using this method, you just sort on the date field and get unknown dates appearing in the correct order, whereas using your method, you have to manipulate dates with '1900-01-01' in order to get them to appear in the correct order.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply