Avatar of nkoriginal
nkoriginalFlag for Spain

asked on 

SQL Select MaxID

Hello Folks, I would like to know if someone know how to select a max ID inside an view sql.
I've 5 tables with inner join (clientes, contactos, conductores, servicios and vehiculos)
I just one row for each client in all tables, LESS vehiculos table, because one cliente can have one or more car.
So when I called this sql, I need to select the last ID for an specific client in vehiculos table.
When I call this procedure I received 4 rows for an specific client, but I need to select the last row so the last one.

Someone know what I requesting??

I attached a jpg to see what I've

Thanks to everyone for any help

Microsoft SQL Server 2005Microsoft SQL Server 2008SQL

Avatar of undefined
Last Comment
Muhammad Kashif
Avatar of awking00
Flag of United States of America image

What makes an ID the "last" one?
post your query which gives you 4 rows for a specific client.
I think that your joins are wrong.
Avatar of Raja Jegan R
Raja Jegan R
Flag of India image

This should do:
Kindly replace All_tables with all tables with JOIN conditions
and all_other_columns with required columns.

It would be better if you provide your current query for easier modification
SELECT all_other_columns
from (
SELECT all_other_columns, Row_number() over ( partition by vehiculos.client order by some_datetimecol desc) rnum
from All_tables) temp
where rnum = 1

Open in new window

you have one or multiple cars against one client. so no of rows will be equal to the no of cars.
so get one car from the vehicle table against one client and then join other tables.

so your query will be like this
CROSS APPLY (SELECT TOP 1 Vehicle FROM Vehiculos WHERE CLIENTES.ClientID = Vehiculos. ClientID) AS Vehicle

Open in new window

Avatar of nkoriginal
Flag of Spain image


Here is the query. I not did it. Is from someone else, but this person is not working here anymore.
So I trying to figure out what he doing.

If nobody can help me is ok... I dont know how to explain 100%

Thanks to try to understand me
SELECT     qcl236.Clientes.CliId, qcl236.Clientes.CliEmpresa, qcl236.Clientes.CliDireccion, qcl236.Clientes.CliPoblacion, qcl236.Clientes.CliProvincia, 
                      qcl236.Clientes.CliCodPos, qcl236.Clientes.CliTelefono1, qcl236.Clientes.CliTelefono2, qcl236.Clientes.CliFax, qcl236.Clientes.CliRecogidaDireccion, 
                      qcl236.Clientes.CliRecogidaPoblacion, qcl236.Clientes.CliRecogidaProvincia, qcl236.Clientes.CliRecogidaCodPos, 
                      qcl236.Clientes.CliRecogidaEmpresa, qcl236.Clientes.CliRecogidaTfno, qcl236.Clientes.CliEntregaDireccion, qcl236.Clientes.CliRecogidaPerCto, 
                      qcl236.Clientes.CliEntregaPoblacion, qcl236.Clientes.CliEntregaProvincia, qcl236.Clientes.CliEntregaCodPos, qcl236.Clientes.CliEntregaEmpresa, 
                      qcl236.Clientes.CliEntregaPerCto, qcl236.Clientes.CliActivo, qcl236.Clientes.CliEntregaTfno, qcl236.Contactos.ConId, qcl236.Contactos.ConCliente, 
                      qcl236.Contactos.ConNombre, qcl236.Contactos.ConMovil, qcl236.Contactos.ConEmail, qcl236.Contactos.ConCambiarPw, qcl236.Contactos.ConTipo, 
                      qcl236.Contactos.ConActivo, qcl236.Contactos.ConQuiereEmails, qcl236.Contactos.ConUsu, qcl236.Contactos.ConPwd, qcl236.Vehiculos.VehId, 
                      qcl236.Vehiculos.VehServicio, qcl236.Vehiculos.VehMarca, qcl236.Vehiculos.VehModelo, qcl236.Vehiculos.VehColor, qcl236.Vehiculos.VehMatricula, 
                      qcl236.Vehiculos.VehRecogidaEmpresa, qcl236.Vehiculos.VehRecogidaDireccion, qcl236.Vehiculos.VehRecogidaPoblacion, 
                      qcl236.Vehiculos.VehRecogidaProvincia, qcl236.Vehiculos.VehRecogidaCodPos, qcl236.Vehiculos.VehRecogidaContacto, 
                      qcl236.Vehiculos.VehRecogidaTfno, qcl236.Vehiculos.VehEntregaEmpresa, qcl236.Vehiculos.VehEntregaDireccion, 
                      qcl236.Vehiculos.VehEntregaPoblacion, qcl236.Vehiculos.VehEntregaProvincia, qcl236.Vehiculos.VehEntregaCodPos, 
                      qcl236.Vehiculos.VehEntregaContacto, qcl236.Vehiculos.VehEntregaTfno, qcl236.Vehiculos.VehComentarioaCliente, 
                      qcl236.Vehiculos.VehComentariodeCliente, qcl236.Vehiculos.VehEntregaComentarioPrivado, qcl236.Vehiculos.VehConductor, 
                      qcl236.Vehiculos.VehFechaPrestacionCliente, qcl236.Vehiculos.VehFechaPrestacionGIF, qcl236.Vehiculos.VehGrupo, 
                      qcl236.Vehiculos.VehDetalleGrupo, qcl236.Vehiculos.VehEstado, qcl236.Vehiculos.VehFechaHoraGrabado, 
                      qcl236.Vehiculos.VehFechaPrestacionClienteAPartirDe, qcl236.Vehiculos.VehKmsEntrega, qcl236.Vehiculos.VehKmsRecogida, 
                      qcl236.Conductores.ConductorId, qcl236.Conductores.ConductorNombre, qcl236.Conductores.ConductorEmail, qcl236.Conductores.ConductorMovil, 
                      qcl236.Servicios.SerId, qcl236.Servicios.SerCliente, qcl236.Servicios.SerContacto, qcl236.Servicios.SerFechaHoraGrabada, 
                      qcl236.Servicios.SerEstado, qcl236.Servicios.SerReferencia, qcl236.Servicios.SerTotalVehiculos, qcl236.Servicios.SerTotalCompletados
FROM         qcl236.Clientes INNER JOIN
                      qcl236.Servicios ON qcl236.Clientes.CliId = qcl236.Servicios.SerCliente INNER JOIN
                      qcl236.Contactos ON qcl236.Servicios.SerContacto = qcl236.Contactos.ConId INNER JOIN
                      qcl236.Vehiculos ON qcl236.Servicios.SerId = qcl236.Vehiculos.VehServicio INNER JOIN
                      qcl236.Conductores ON qcl236.Vehiculos.VehConductor = qcl236.Conductores.ConductorId

Open in new window

Avatar of Muhammad Kashif
Muhammad Kashif
Flag of Pakistan image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Microsoft SQL Server 2005
Microsoft SQL Server 2005

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo