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

view-sql.jpg

awking00

What makes an ID the "last" one?

Muhammad Kashif

post your query which gives you 4 rows for a specific client.
I think that your joins are wrong.

Raja Jegan R

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

Muhammad Kashif

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

SELECT CLIENTES.*, Vehicle.Column FROM CLIENTES
CROSS APPLY (SELECT TOP 1 Vehicle FROM Vehiculos WHERE CLIENTES.ClientID = Vehiculos. ClientID) AS Vehicle

Open in new window

nkoriginal

ASKER

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

ASKER CERTIFIED SOLUTION

Muhammad Kashif

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial